Friday, March 08, 2013 3:07 PM
I have two tables. Table1 and Table2.
I want to add a new column in Table1, which is the primary key in Table2. My script:
ALTER TABLE Table1 ADD TPkey bigint NOT NULL ADD CONSTRAINT Table1_TPkey FOREIGN KEY(TPkey) REFERENCES Table2(pkey)
Thanks for help.
- Edited by ardmore Friday, March 08, 2013 3:40 PM add
Friday, March 08, 2013 3:22 PM
Try this. Note that the column TPKey needs to exist
ALTER TABLE Table1
ADD CONSTRAINT TPKey FOREIGN KEY(TPKey) REFERENCES Table2(pkey)
Friday, March 08, 2013 3:30 PMModeratorWhat error did you get with the above?
For every expert, there is an equal and opposite expert. - Becker's Law
Friday, March 08, 2013 3:30 PMTPKey is a new column. Need to add it.
Friday, March 08, 2013 3:35 PMIncorreect syntax near 'CONSTRAINT', expecting ID
Friday, March 08, 2013 4:27 PM
You only need the keyword ADD once in the alter statement. If you add a NOT NULL column you must specify a default value. (Your choices are 1) allow the new columns to have NULL, 2) specify a default value in the ALTER statement that adds the column, or 3) Add the column as NOT NULL, run UPDATE command(s) that load a value into that column in every row and then do another ALTER that changes that column to NOT NULL). But sample code to do this could be
use tempdb go Create Table T1(T2PK int primary key); Create Table T2(T2PK int primary key); Alter Table T1 Add TPkey int null, Constraint FKConst Foreign Key(TPkey) References T2(T2PK); go Drop Table T1; Drop Table T2;
- Marked As Answer by ardmore Friday, March 08, 2013 6:31 PM