Results 1 to 5 of 5

Thread: Foreign key

  1. #1
    Join Date
    Sep 2004
    Posts
    21

    Unanswered: Foreign key

    Hello,
    I need to add a column to a table. The new column is a foreign key which references another table's column.

    how can you do this? Does it need to be done in two steps, like:

    alter table table1
    add new_col_name datatype

    then

    alter table table1
    add table constraint.

    If so, what is the syntax for adding a foreign key contraint which references another table.

    thank you

  2. #2
    Join Date
    Aug 2004
    Location
    France
    Posts
    754
    Hello,

    Yes, it has to be done in two steps, just as you said.

    To add a foreign key constraint :

    Code:
    ALTER TABLE table1 
    ADD CONSTRAINT fk_table1_table2
    FOREIGN KEY (field1) 
    REFERENCES table2(field2);
    Where field1 is the column in table1 which is referenced by the column field2 in table2. If you have multi-column FK constraints, just put them in the right order, separated by commas :
    Code:
    ALTER TABLE table1 
    ADD CONSTRAINT fk_table1_table2
    FOREIGN KEY (field11, field12) 
    REFERENCES table2(field21, field22);
    Regards,

    RBARAER

  3. #3
    Join Date
    Sep 2004
    Posts
    21
    thanks! what does the fk_table1_table2 mean? Is it just a lable?

  4. #4
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    It can be done in one step, at least it can on Oracle:
    Code:
    alter table table1
    add (new_col_name references table2(keycol));
    or (to give the constraint a specific name):

    Code:
    alter table table1
    add (new_col_name constraint table1_table2_fk references table2(keycol));

  5. #5
    Join Date
    Sep 2004
    Posts
    21
    cool, I'll try that also, but the first code worked.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •