Add new column with foreign key constraint in one command

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.


In MS-SQLServer:

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

Tags:

Sql