Add a new column with a foreign key in one command

Question

I am trying to add a new column which will be a foreign key. I managed to add a column and a 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 think of anything that works.

Answer 1

As often with a SQL related question, it depends on the DBMS. Some DBMSs allow combining ALTER table operations, separated by commas. For example...

Informix :

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

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

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. Standard SQL allows only one operation in an ALTER TABLE statement, so in standard SQL this must be done in two steps.

Answer 2

In MS-SQLServer:

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

Answer 3

For SQL Server, it should be something like

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

Answer 4

In MS SQL SERVER:

With a user-defined foreign key name

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

Without a user-defined foreign key name

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

Answer 5

In Oracle :

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

Answer 6

You can do this as shown below in SQL Server

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

Answer 7

PostgreSQL DLL to add an FK column:

 ALTER TABLE one ADD two_id INTEGER REFERENCES two; 

Answer 8

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

Answer 9

For DB2 syntax:

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

Answer 10

2018 Update

This is a pretty old question, but people are still returning to it, I see. If the answers above did not help you, make sure you use the same data type for the new column as the identifier of the other table.

In my case, I used Laravel and use the "unsigned integer" for all of my identifiers, since it makes no sense to have a negative LOL identifier.

To do this, the raw SQL query will change as follows:

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

I hope this helps

Answer 11

If you also need to add default values, if you already have several rows in the table, add DEFAULT val

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