Add new column with foreign key constraint in one command

Solution 1:

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.

Solution 2:

In MS-SQLServer:

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

Solution 3:

For SQL Server it should be something like

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

Solution 4:

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);