ALTER TABLE (ADD column question)

Hi All,
I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
column with values?
Have any way to do this?
SQL Server 2005
--
ThanksHi
I cannot test it on SQL Server 2005 right now but I did some testing on SQL
Server 2000
CREATE TABLE #Test (col1 INT)
--Insert some data
INSERT INTO #Test VALUES (1)
INSERT INTO #Test VALUES (2)
--Alter table
ALTER TABLE #Test ADD col2 INT IDENTITY(1,1) NOT NULL
GO
ALTER TABLE #Test ADD CONSTRAINT my_coms UNIQUE NONCLUSTERED (col2)
"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:%23xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>|||"ReTF" <re.tf@.newsgroup.nospam> wrote in message
news:<#xqMSNGEGHA.2708@.TK2MSFTNGP11.phx.gbl>...
> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
You can add a non-nullable column by specifying a default and then dropping
it afterwards. Example:
ALTER TABLE tbl
ADD x INTEGER NOT NULL
CONSTRAINT df_tbl_x DEFAULT (0) ;
ALTER TABLE tbl DROP CONSTRAINT df_tbl_x ;
As for adding the unique constraint, obviously you'll have to populate the
column with unique values first. You haven't told us what this data is or
where it comes from so it's hard to help you with that. Is this supposed to
be a surrogate key? Are you aware of the IDENTITY feature in SQL Server?
David Portas
SQL Server MVP
--|||As David Says, If you want to add a new column which is not null, you MUSt
provide a default non null value - otherwise what will the value for the
existing value for rows be - except null... Afterwords, you can drop the
default if you wish...
This may run long, becuase will have to re-write all of the existing rows.
Also watch your tran log..
--
Wayne Snyder MCDBA, SQL Server MVP
Mariner, Charlotte, NC
I support the Professional Association for SQL Server ( PASS) and it''s
community of SQL Professionals.
"ReTF" wrote:

> Hi All,
> I need add one column in one table, but this table already have rows, and
> this new column need be NOT NULL and UNIQUE CONSTRAINT, how I can add this
> column with values?
> Have any way to do this?
> --
> SQL Server 2005
> --
> Thanks
>
>|||>> I need add one column in one table, but this table already have rows, and
this new column need be NOT NULL and UNIQUE , how I can add this column wit
h values? <<
You can use an ALTER TABLE to add the columns. If you also have a
DEFAULT, you will get a single value; if not, you will get a NULL. Use
the NULL, so you can find problems after the UPDATE.
You have a serious problem because someone missed a key in their data
model. You will need to update the new column with the new key, based
on some rule that matches it to the existing key.
Once those values are in place, you then need to check to see that
there are no NULLs and that all values are unique. Then use another
ALTER TABLE to add UNIQUE NOT NULL constraints.
I did this once when merging two inventory systems that used different
part numbers for the same items. It is a pain and you will probalby
have some errors.|||> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
Not unless you use the IDENTITY property or NEWID().
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials
"--CELKO--" <jcelko212@.earthlink.net> wrote in message
news:1136385177.110647.17120@.o13g2000cwo.googlegroups.com...
> You can use an ALTER TABLE to add the columns. If you also have a
> DEFAULT, you will get a single value; if not, you will get a NULL. Use
> the NULL, so you can find problems after the UPDATE.
> You have a serious problem because someone missed a key in their data
> model. You will need to update the new column with the new key, based
> on some rule that matches it to the existing key.
> Once those values are in place, you then need to check to see that
> there are no NULLs and that all values are unique. Then use another
> ALTER TABLE to add UNIQUE NOT NULL constraints.
> I did this once when merging two inventory systems that used different
> part numbers for the same items. It is a pain and you will probalby
> have some errors.
>