Back

Explore Courses Blog Tutorials Interview Questions
0 votes
2 views
in SQL by (20.3k points)

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.

1 Answer

0 votes
by (40.7k points)

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.

Related questions

Welcome to Intellipaat Community. Get your technical queries answered by top developers!

30.4k questions

32.5k answers

500 comments

108k users

Browse Categories

...