[rmill9681]

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

[RBARAER]

Hello,

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

To add a foreign key constraint :

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 :

ALTER TABLE table1 
ADD CONSTRAINT fk_table1_table2
FOREIGN KEY (field11, field12) 
REFERENCES table2(field21, field22);

Regards,

RBARAER

[rmill9681]

thanks! what does the fk_table1_table2 mean? Is it just a lable?

[andrewst]

It can be done in one step, at least it can on Oracle:

alter table table1
add (new_col_name references table2(keycol));

or (to give the constraint a specific name):

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

[rmill9681]

cool, I'll try that also, but the first code worked.