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

User · Answer

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

User · Answer

You can do it like below in SQL Server  ALTER TABLE one ADD two id int foreign key REFERENCES two id

User · Answer

PostgreSQL DLL to add an FK column   ALTER TABLE one ADD two id INTEGER REFERENCES two

User · Answer

ALTER TABLE TableName  ADD NewColumnName INTEGER   FOREIGN KEY NewColumnName  REFERENCES  ForeignKey TableName  Foreign Key Column

User · Answer

In MS-SQLServer   ALTER TABLE one ADD two id integer CONSTRAINT fk FOREIGN KEY  two id  REFERENCES two id

User · Answer

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

User · Answer

For DB2  the syntax is   ALTER TABLE one ADD two id INTEGER FOREIGN KEY  two id  REFERENCES two  id

User · Answer

2020 Update  It s pretty old question but people are still returning to it I see  In case the above answers did not help you  make sure that you are using same data type for the new column as the id of the other table   In my case  I was using Laravel and I use  unsigned integer  for all of my ids as there is no point of having negative id LOL   So for that  the raw SQL query will change like this   ALTER TABLE  table name  ADD  column name  INTEGER UNSIGNED  ADD CONSTRAINT constrain name FOREIGN KEY column name  REFERENCES foreign table name id     I hope it helps

User · Answer

For SQL Server it should be something like  ALTER TABLE one ADD two id integer constraint fk foreign key references two id

User · Answer

In Oracle    ALTER TABLE one ADD two id INTEGER CONSTRAINT Fk two id REFERENCES two id

User · Answer

Try this    ALTER TABLE product ADD FOREIGN KEY  product ID  REFERENCES product product ID

User · Answer

If you also need to add default values in case you already have some rows in the table then add DEFAULT val  ALTER TABLE one ADD two id int DEFAULT 123  FOREIGN KEY two id  REFERENCES two id

[sql] Add new column with foreign key constraint in one command

Examples related to sql