Click here to Skip to main content
15,938,848 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have table that contain some rows.
I want to add a bigint data type column with not allow null & assign a default value '0'
But arise a error msg.
The error msg is 'ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]ALTER TABLE only allows columns to be added that can contain nulls or have a DEFAULT definition specified. Column 'Column_name' cannot be added to table 'Table_Name' because it does not allow nulls and does not specify a DEFAULT definition.'

How will i do to assign a not allow nulls and does not specify a DEFAULT definition in existing table??
Please help me any instruction for solving......
Posted

You can't.

Since you have existing data, when you add a column the existing data has to be extended to include the new info. If it can't be null, then the new values have to come from somewhere, and that place has to be the default value.

Your only other alternative is to delete the table, and recreate it before re-populating it with the current info - but then you will have to work out what each row new value should be. I.e. you will have to work out the "default value" yourself.
 
Share this answer
 
Adding NOT NULL Columns as an Online Operation


In SQL Server 2012 Enterprise Edition, adding a NOT NULL column with a default value is an online operation when the default value is a runtime constant. This means that the operation is completed almost instantaneously regardless of the number of rows in the table. This is because the existing rows in the table are not updated during the operation; instead, the default value is stored only in the metadata of the table and the value is looked up as needed in queries that access these rows. This behavior is automatic; no additional syntax is required to implement the online operation beyond the ADD COLUMN syntax. A runtime constant is an expression that produces the same value at runtime for each row in the table regardless of its determinism. For example, the constant expression "My temporary data", or the system function GETUTCDATETIME() are runtime constants. In contrast, the functions NEWID() or NEWSEQUENTIALID() are not runtime constants because a unique value is produced for each row in the table. Adding a NOT NULL column with a default value that is not a runtime constant is always performed offline and an exclusive (SCH-M) lock is acquired for the duration of the operation.

While the existing rows reference the value stored in metadata, the default value is stored on the row for any new rows that are inserted and do not specify another value for the column. The default value stored in metadata is moved to an existing row when the row is updated (even if the actual column is not specified in the UPDATE statement), or if the table or clustered index is rebuilt.

Columns of type varchar(max), nvarchar(max), varbinary(max), xml, text, ntext, image, hierarchyid, geometry, geography, or CLR UDTS, cannot be added in an online operation. A column cannot be added online if doing so causes the maximum possible row size to exceed the 8,060 byte limit. The column is added as an offline operation in this case.


Source: ALTER TABLE (Transact-SQL)[^]
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900