Add new column with foreign key constraint in one command

Question

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.

Answer 1

Mostly SQL-related question depends on the DBMS. Some DBMS allow us to combine ALTER table operations separated by commas. 

Informix syntax is as follows:

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 is as follows:

ALTER TABLE one

ADD two_id INTEGER,

FOREIGN KEY(two_id) REFERENCES two(id);

Note: Standard SQL allows a single operation in the ALTER TABLE statement, therefore in Standard SQL, it should be done in two steps.

Answer 2

Yes, you can add a new column with a foreign key constraint in a single ALTER TABLE command.

ALTER TABLE tableA ADD newColumnId INTEGER, ADD CONSTRAINT fkNewColumn FOREIGN KEY (newColumnId) REFERENCES tableB(refId);