none
Add column and establish foreign key relationships.

    Question

  • 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:07 PM

Answers

  • 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;

    Tom

    • Marked as answer by ardmore Friday, March 08, 2013 6:31 PM
    Friday, March 08, 2013 4:27 PM

All replies

  • 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:22 PM
  • What error did you get with the above?

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog

    Friday, March 08, 2013 3:30 PM
  • TPKey is a new column. Need to add it.
    Friday, March 08, 2013 3:30 PM
  • Incorreect syntax near 'CONSTRAINT', expecting ID
    Friday, March 08, 2013 3:35 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;

    Tom

    • Marked as answer by ardmore Friday, March 08, 2013 6:31 PM
    Friday, March 08, 2013 4:27 PM