172

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.

Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
mushroom
  • 6,201
  • 5
  • 36
  • 63
  • 3
    In MYSQL: http://stackoverflow.com/questions/1545253/mysql-adding-column-and-foreign-keys#answer-20758448 – knocte Apr 22 '16 at 04:21

14 Answers14

234

As so often with SQL-related question, it depends on the DBMS. Some DBMS allow you to combine ALTER TABLE operations separated by commas. For example...

Informix syntax:

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

The syntax for IBM DB2 LUW is similar, repeating the keyword ADD but (if I read the diagram correctly) not requiring a comma to separate the added items.

Microsoft SQL Server syntax:

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

Some others do not allow you to combine ALTER TABLE operations like that. Standard SQL only allows a single operation in the ALTER TABLE statement, so in Standard SQL, it has to be done in two steps.

Pathros
  • 10,042
  • 20
  • 90
  • 156
Jonathan Leffler
  • 730,956
  • 141
  • 904
  • 1,278
  • 5
    Second Add and constraint. – Imran Sep 19 '14 at 19:28
  • 32
    For the SQL part, to give name to the constraint, you write it like this: ALTER TABLE [Messages] ADD [AlertTriggerID] BIGINT NULL, CONSTRAINT FK_Messages_AlertTriggers FOREIGN KEY (AlertTriggerID) REFERENCES AlertTriggers (AlertTriggerID); – Dragos Durlut Jan 21 '16 at 12:36
  • MySQL syntax: https://stackoverflow.com/questions/1545253/how-to-add-a-column-and-make-it-a-foreign-key-in-single-mysql-statement#answer-20758448 – Yves M. Dec 29 '21 at 09:40
107

In MS-SQLServer:

ALTER TABLE one
ADD two_id integer CONSTRAINT fk FOREIGN KEY (two_id) REFERENCES two(id)
knocte
  • 16,941
  • 11
  • 79
  • 125
sqladmin
  • 2,079
  • 2
  • 16
  • 11
  • I don't think the question needs clarifying, as the accepted answer already states that there's a big difference between DB engines in how to do it, what needs to be clarified is your answer, so I just did that – knocte Apr 22 '16 at 07:10
  • 16
    Up for naming your constraint. The accepted answer lets the system generate the name, which is icky and hard to manage later. – Derpy May 26 '16 at 17:08
24

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);
Baum mit Augen
  • 49,044
  • 25
  • 144
  • 182
Sandeep Badikolu
  • 241
  • 3
  • 5
18

For SQL Server it should be something like

ALTER TABLE one
ADD two_id integer constraint fk foreign key references two(id)
jausel
  • 594
  • 7
  • 18
6

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

Abhay Maurya
  • 11,819
  • 8
  • 46
  • 64
5

In Oracle :

ALTER TABLE one ADD two_id INTEGER CONSTRAINT Fk_two_id REFERENCES two(id);
O.Badr
  • 2,853
  • 2
  • 27
  • 36
4

PostgreSQL DLL to add an FK column:

ALTER TABLE one
ADD two_id INTEGER REFERENCES two;
Dariusz
  • 21,561
  • 9
  • 74
  • 114
Kshitij Bajracharya
  • 811
  • 2
  • 14
  • 37
2

For DB2, the syntax is:

ALTER TABLE one ADD two_id INTEGER FOREIGN KEY (two_id) REFERENCES two (id);
Dan B.
  • 1,451
  • 2
  • 14
  • 23
1

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

Arsman Ahmad
  • 2,000
  • 1
  • 26
  • 34
1

For SQL Server with constraint name

ALTER TABLE one
ADD two_id INT NOT NULL,
CONSTRAINT FK_name FOREIGN KEY (two_id) REFERENCES two(id);
Madhu Cheepati
  • 809
  • 5
  • 12
0

You can do it like below in SQL Server

ALTER TABLE one
ADD two_id int foreign key
REFERENCES two(id)
Ajay2707
  • 5,690
  • 6
  • 40
  • 58
user2010014
  • 15
  • 1
  • 2
0

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);
iMalek
  • 189
  • 1
  • 14
0

Try this: 

ALTER TABLE product
ADD FOREIGN KEY (product_ID) REFERENCES product(product_ID);
Nikos Tavoularis
  • 2,843
  • 1
  • 30
  • 27
Michael Alavanyo
  • 1
  • 1
  • 1
    Welcome to SO! Please, comment your answers when they are just code. In your case, there are many answers quite similar to yours, so expose the benefits of yours. – David García Bodego Dec 28 '19 at 01:22
0

In MS SQL SERVER:

IF NOT EXISTS (
SELECT  TOP 1 1  
FROM    INFORMATION_SCHEMA.COLUMNS 
WHERE   TABLE_SCHEMA = 'dbo' 
AND     TABLE_NAME = 'yourTable' 
AND     COLUMN_NAME = 'yourNewColumn')
BEGIN
    ALTER TABLE yourTable ADD yourNewColumn INT NOT NULL 
    CONSTRAINT DF_yourTable_yourNewColumn DEFAULT (1) 
    CONSTRAINT FK_yourTable_lookupTable FOREIGN KEY (yourNewColumn) REFERENCES LookupTable(Id)
    ON UPDATE  NO ACTION 
    ON DELETE  NO ACTION 
END
GO
Boris Handžiski
  • 1