adding a column with not null - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

adding a column with not null

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-09-04 : 01:46:24

Hi All,
I've one table named tableAB. in that i've added one new column with not null option in the enterprise manager. then i've generated the script, and run the script in client database. because already data is there, it is not accepting to put null value in the new column. so the is missing. anyway backup is there with me.

what is the solution for this....

thanks in advance

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 02:42:03

Did you add the "DEFAULT <value>" statement?

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-04 : 06:25:39

or
export data to another table
empty source table
alter source table with NULL column
import data from exported table

Madhivanan

Failing to plan is Planning to fail

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-09-04 : 06:37:31

i've added default value, like i've placed '0' at default value option of design table option in enterprise manager.

but this is giving error while i've generated script and run in query analyser regarding constraints....

Vinod
Even you learn 1%, Learn it with 100% confidence.

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 07:11:21

Sounds like something else that has changed is triggering the error, not the new NOT NULL column

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2007-09-04 : 07:24:35

i've solved this......

after generating the script, i've pasted that code in QA, and then i've added the new column name in the insert query and in the select query, i've placed 0 as the default value.....
now the script is making the table with all data as well as with the new column with the default value....

actually i need to add the column in the middle of the table...

Vinod
Even you learn 1%, Learn it with 100% confidence.

Kristen
Test

22859 Posts

Posted - 2007-09-04 : 07:31:37

Bizarre, because if the column has a defined DEFAULT value it should be able to be left out of the INSERT statement altogether.

if you really do want a default, rather than just a value for "pre-existing rows", then probably best to check that there really is a Default on that column!

(Maybe the Default was being created LATER in the script, which would be no good of course!!)

Kristen

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 07:36:00

[code]CREATE TABLE #Temp
(
PK INT IDENTITY,
[Value] INT
)
GO

SELECT * FROM #Temp

INSERT #Temp VALUES (99)

SELECT * FROM #Temp

ALTER TABLE #Temp ADD c1 INT DEFAULT 0

SELECT * FROM #Temp

ALTER TABLE #Temp ADD c2 INT NOT NULL DEFAULT 0

SELECT * FROM #Temp

DROP TABLE #Temp[/code]

E 12°55'05.25"
N 56°04'39.16"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2007-09-04 : 07:39:11

And copied from Books Online (ALTER TABLE search)

quote:
CREATE TABLE doc_exf ( column_a INT) ;
GO
select * from doc_exf
go
INSERT INTO doc_exf
VALUES (1) ;
GO
select * from doc_exf
go
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES ;
GO
select * from doc_exf
go
DROP TABLE doc_exf ;
GO

E 12°55'05.25"
N 56°04'39.16"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2007-09-05 : 06:45:08

<<
actually i need to add the column in the middle of the table...
>>

Why does it matter?

Madhivanan

Failing to plan is Planning to fail

Kristen
Test

22859 Posts

Posted - 2007-09-05 : 08:16:44

"Why does it matter?"

I tend to do that too, so that columns that in some way "go together" are adjacent in the column list.

Of course if the Database Tools you are using displays them in alphabetical order then it doesn't matter!

Kristen

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources