[sql] Add new column with foreign key constraint in one command

I am trying to add a new column that will be a foreign key. I have been able to add the column and the foreign key constraint using two separate ALTER TABLE commands:

ALTER TABLE one
ADD two_id integer;

ALTER TABLE one
ADD FOREIGN KEY (two_id) REFERENCES two(id);

Is there a way to do this with one ALTER TABLE command instead of two? I could not come up with anything that works.

This question is related to sql

The answer is


In Oracle :

ALTER TABLE one ADD two_id INTEGER CONSTRAINT Fk_two_id REFERENCES two(id);

In MS SQL SERVER:

With user defined foreign key name

ALTER TABLE tableName
ADD columnName dataType,
CONSTRAINT fkName FOREIGN KEY(fkColumnName) 
   REFERENCES pkTableName(pkTableColumnName);

Without user defined foreign key name

ALTER TABLE tableName
ADD columnName dataType,
FOREIGN KEY(fkColumnName) REFERENCES pkTableName(pkTableColumnName);

PostgreSQL DLL to add an FK column:

ALTER TABLE one
ADD two_id INTEGER REFERENCES two;

2020 Update

It's pretty old question but people are still returning to it I see. In case the above answers did not help you, make sure that you are using same data type for the new column as the id of the other table.

In my case, I was using Laravel and I use "unsigned integer" for all of my ids as there is no point of having negative id LOL.

So for that, the raw SQL query will change like this:

ALTER TABLE `table_name`
ADD `column_name` INTEGER UNSIGNED,
ADD CONSTRAINT constrain_name FOREIGN KEY(column_name) REFERENCES foreign_table_name(id);

I hope it helps


If you also need to add default values in case you already have some rows in the table then add DEFAULT val

ALTER TABLE one
ADD two_id int DEFAULT 123,
FOREIGN KEY(two_id) REFERENCES two(id);

In MS-SQLServer:

ALTER TABLE one
ADD two_id integer CONSTRAINT fk FOREIGN KEY (two_id) REFERENCES two(id)

For SQL Server it should be something like

ALTER TABLE one
ADD two_id integer constraint fk foreign key references two(id)

Try this:

ALTER TABLE product
ADD FOREIGN KEY (product_ID) REFERENCES product(product_ID);

You can do it like below in SQL Server

ALTER TABLE one
ADD two_id int foreign key
REFERENCES two(id)

ALTER TABLE TableName ADD NewColumnName INTEGER, FOREIGN KEY(NewColumnName) REFERENCES [ForeignKey_TableName](Foreign_Key_Column)


For DB2, the syntax is:

ALTER TABLE one ADD two_id INTEGER FOREIGN KEY (two_id) REFERENCES two (id);