How to add column to an already populated table

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

SQL Server 2005 Forums

Transact-SQL (2005)

How to add column to an already populated table

Author

Topic

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 07:58:00

I just tried to add a column to an already populated table but I get an error message.

Here's what I tried to execute :

ALTER TABLE buildings
ADD admin_id nvarchar(70) NOT NULL,
CONSTRAINT buildings_fk2 FOREIGN KEY (admin_id) REFERENCES role1
ON UPDATE CASCADE ON DELETE NO ACTION

and here's the message I've got :

Msg 4901, Level 16, State 1, Line 1
ALTER TABLE only allows columns to be added that can contain nulls, or have a DEFAULT definition specified, or the column being added is an identity or timestamp column, or alternatively if none of the previous conditions are satisfied the table must be empty to allow addition of this column. Column '?O????S_????????S??' cannot be added to non-empty table 'buildings' because it does not satisfy these conditions.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-01 : 08:01:49

you are specifiying NOT NULL. all rows in the new column are null and thus violate that constraint.
you have to specify the default value for it.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 08:19:25

Now that I have added the column with null values I can't drop it.
I tried to firstly drop the constraint but no luck at all.
Any ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-01 : 08:21:01

maybe you should tell us what exactly are you trying to acomplish.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 08:28:35

Ok I managed to drop the table.It was a matter of wrong syntax from my side.
What I want to do now is to create a relation between a new table I have created and the buildings table but that relation must be one-to-one so the new column in the buildings table must be not null unique.
How can I do that?
Thank you very much for your help.

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2008-12-01 : 08:31:31

there is no one to one relationship in sql server. only one to many.
you'll have to create either a check constraint to fail in value already exists when trying to insert new value or handle this in your app.

___________________________________________________________________________
Causing trouble since 1980
Blog: http://weblogs.sqlteam.com/mladenp
Speed up SSMS development: www.ssmstoolspack.com <- version 1.1 out!

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 08:54:43

I see.
In the previous situation if I fill all the rows with values can I then alter the new column and make it not null?
Thanks again for the help.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:02:14

quote:
Originally posted by skiabox

I see.
In the previous situation if I fill all the rows with values can I then alter the new column and make it not null?
Thanks again for the help.

yup you can. the value is specified by means of a DEFAULT constraint. something like

ALTER TABLE buildings
ADD admin_id nvarchar(70) NOT NULL DEFAULT <yourdefaultvaluehere>

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 09:09:16

So my friend visakh16 you suggest that I drop again the column and enter it again with you command?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:15:25

quote:
Originally posted by skiabox

So my friend visakh16 you suggest that I drop again the column and enter it again with you command?

so now you've created column as NULLable?

then just use


ALTER TABLE buildings
ALTER COLUMN admin_id nvarchar(70) NOT NULL DEFAULT <yourdefaultvaluehere>

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 09:23:19

I tried the following :

ALTER TABLE buildings
ALTER COLUMN admin_id nvarchar(70) NOT NULL DEFAULT 'NOT SET'

and I am getting the following error message :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 09:26:14

Another question visakh16 : can I set this new column to not null unique?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 09:27:58

quote:
Originally posted by skiabox

I tried the following :

ALTER TABLE buildings
ALTER COLUMN admin_id nvarchar(70) NOT NULL DEFAULT 'NOT SET'

and I am getting the following error message :
Msg 156, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'DEFAULT'.

ah , i think now that column is created already you need this

UPDATE buildings SET admin_id=N'NOT SET'

ALTER TABLE buildings
ALTER COLUMN admin_id nvarchar(70) NOT NULL

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-01 : 13:24:40

Have you seen my last question?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-01 : 23:44:08

quote:
Originally posted by skiabox

Have you seen my last question?

Yup. you can
make it not null as per earlier post.
Then update the column with unique values based on your rule
then add a unique constraint to it using ALTER TABLE ADD CONSTRAINT

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-03 : 06:06:33

ok.that is the last question I make in this thread.
I have created a table named role1.
This table has 5 fields.
I have made the first of these fields (admin_id) primary.

Now I have one already populated table named buildings.
This table is composed of several fields.
There is a primary key in this table called building_id (it is also IDENTITY(1,1)).
What I want to do is add a foreign key in this table which will be called admin_id and it will connect the two tables.
What's the best way to achieve this?
Thank you very much all for your help!

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2008-12-03 : 13:09:55

I'm not sure if it add anything to conversation, but it is good practice to name your constraints (even default constraints). Here are two example of adding a new column to a table named Foo. The first one is just adding a NOT NULL column with a default value and the second is adding the same column with a names default constraint:

ALTER TABLE Foo
ADD Bar INT NOT NULL DEFAULT (0)

ALTER TABLE Foo
ADD Bar INT NOT NULL, CONSTRAINT DF_Foo_Bar DEFAULT (1) FOR Bar

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-03 : 13:16:26

quote:
Originally posted by skiabox

ok.that is the last question I make in this thread.
I have created a table named role1.
This table has 5 fields.
I have made the first of these fields (admin_id) primary.

Now I have one already populated table named buildings.
This table is composed of several fields.
There is a primary key in this table called building_id (it is also IDENTITY(1,1)).
What I want to do is add a foreign key in this table which will be called admin_id and it will connect the two tables.
What's the best way to achieve this?
Thank you very much all for your help!

just add the constraint to table buildings on column admin_id using ALTER TABLE ADD CONSTRAINT... statement. for this
1. columns admin_id should exist
2. If it has values it should values present in corresponding column of role1

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-03 : 17:43:42

Shouldn't I first create the column admin_id in the buildings table before I add the constraint?

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-03 : 17:52:20

what do you think of this code ?

-- add new column with default value
ALTER TABLE dbo.Products ADD
product_grams int NOT NULL CONSTRAINT DF_Products_product_grams DEFAULT 100
GO

-- drop constraint to set default value
ALTER TABLE dbo.Products
DROP CONSTRAINT DF_Products_product_grams
GO

skiabox
Posting Yak Master

169 Posts

Posted - 2008-12-03 : 18:00:12

Guys I just tried the above code and it works like a charm!

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources