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)