How do I create a foreign key in SQL Server

Question

I have never  hand-coded  object creation code for SQL Server and foreign key decleration is seemingly different between SQL Server and Postgres  Here is my sql so far   drop table exams  drop table question bank  drop table anwser bank   create table exams       exam id uniqueidentifier primary key      exam name varchar 50      create table question bank       question id uniqueidentifier primary key      question exam id uniqueidentifier not null      question text varchar 1024  not null      question point value decimal      constraint question exam id foreign key references exams exam id     create table anwser bank       anwser id           uniqueidentifier primary key      anwser question id  uniqueidentifier      anwser text         varchar 1024       anwser is correct   bit      When I run the query I get this error      Msg 8139  Level 16  State 0  Line 9   Number of referencing columns in   foreign key differs from number of   referenced columns  table    question bank     Can you spot the error

User · Answer

I always use this syntax to create the foreign key constraint between 2 tables

Alter Table ForeignKeyTable
Add constraint `ForeignKeyTable_ForeignKeyColumn_FK`
`Foreign key (ForeignKeyColumn)` references `PrimaryKeyTable (PrimaryKeyColumn)`

i.e.

Alter Table tblEmployee
Add constraint tblEmployee_DepartmentID_FK
foreign key (DepartmentID) references tblDepartment (ID)

User · Answer

create table question bank       question id uniqueidentifier primary key      question exam id uniqueidentifier not null constraint fk exam id foreign key references exams exam id       question text varchar 1024  not null      question point value decimal      --That will work too  Pehaps a bit more intuitive construct

User · Answer

You can also name your foreign key constraint by using   CONSTRAINT your name here FOREIGN KEY  question exam id  REFERENCES EXAMS  exam id

User · Answer

If you want to create two table s columns into a relationship by using a query try the following   Alter table Foreign Key Table name add constraint  Foreign Key Table name Columnname FK Foreign Key  Column name  references  Another Table name Another Table Column name

User · Answer

Necromancing  Actually  doing this correctly is a little bit trickier    You first need to check if the primary-key exists for the column you want to set your foreign key to reference to    In this example  a foreign key on table T ZO SYS Language Forms is created  referencing dbo T SYS Language Forms LANG UID   -- First  chech if the table exists    IF 0  lt        SELECT COUNT    FROM INFORMATION SCHEMA TABLES      WHERE TABLE TYPE    BASE TABLE      AND TABLE SCHEMA    dbo      AND TABLE NAME    T SYS Language Forms    BEGIN     -- Check for NULL values in the primary-key column     IF 0    SELECT COUNT    FROM T SYS Language Forms WHERE LANG UID IS NULL      BEGIN         ALTER TABLE T SYS Language Forms ALTER COLUMN LANG UID uniqueidentifier NOT NULL           -- No  don t drop  FK references might already exist            -- Drop PK if exists          -- ALTER TABLE T SYS Language Forms DROP CONSTRAINT pk constraint name          --DECLARE  pkDropCommand nvarchar 1000           --SET  pkDropCommand   N ALTER TABLE T SYS Language Forms DROP CONSTRAINT     QUOTENAME  SELECT CONSTRAINT NAME FROM INFORMATION SCHEMA TABLE CONSTRAINTS          --WHERE CONSTRAINT TYPE    PRIMARY KEY           --AND TABLE SCHEMA    dbo           --AND TABLE NAME    T SYS Language Forms           ----AND CONSTRAINT NAME    PK T SYS Language Forms           --           ---- PRINT  pkDropCommand          --EXECUTE  pkDropCommand            -- Instead do         -- EXEC sp rename  dbo T SYS Language Forms PK T SYS Language Forms1234565    PK T SYS Language Forms             -- Check if they keys are unique  it is very possible they might not be           IF 1  gt    SELECT TOP 1 COUNT    AS cnt FROM T SYS Language Forms GROUP BY LANG UID ORDER BY cnt DESC          BEGIN              -- If no Primary key for this table             IF 0                                   SELECT COUNT    FROM INFORMATION SCHEMA TABLE CONSTRAINTS                  WHERE CONSTRAINT TYPE    PRIMARY KEY                   AND TABLE SCHEMA    dbo                   AND TABLE NAME    T SYS Language Forms                   -- AND CONSTRAINT NAME    PK T SYS Language Forms                                 ALTER TABLE T SYS Language Forms ADD CONSTRAINT PK T SYS Language Forms PRIMARY KEY CLUSTERED  LANG UID ASC                             -- Adding foreign key             IF 0    SELECT COUNT    FROM INFORMATION SCHEMA REFERENTIAL CONSTRAINTS WHERE CONSTRAINT NAME    FK T ZO SYS Language Forms T SYS Language Forms                    ALTER TABLE T ZO SYS Language Forms WITH NOCHECK ADD CONSTRAINT FK T ZO SYS Language Forms T SYS Language Forms FOREIGN KEY ZOLANG LANG UID  REFERENCES T SYS Language Forms LANG UID            END -- End uniqueness check         ELSE             PRINT  FSCK  this column has duplicate keys  and can thus not be changed to primary key          END -- End NULL check     ELSE         PRINT  FSCK  need to figure out how to update NULL value s       END

User · Answer

To Create a foreign key on any table   ALTER TABLE  SCHEMA   TABLENAME  ADD FOREIGN KEY  COLUMNNAME  REFERENCES  TABLENAME  COLUMNNAME  EXAMPLE ALTER TABLE  dbo   UserMaster  ADD FOREIGN KEY  City Id  REFERENCES  dbo   CityMaster  City Id

User · Answer

Like you  I don t usually create foreign keys by hand  but if for some reason I need the script to do so I usually create it using ms sql server management studio and before saving then changes   I select Table Designer   Generate Change Script

User · Answer

create table question bank       question id uniqueidentifier primary key      question exam id uniqueidentifier not null      question text varchar 1024  not null      question point value decimal      constraint fk questionbank exams foreign key  question exam id  references exams  exam id

User · Answer

This script is about creating tables with foreign key and I added referential integrity constraint sql-server   create table exams         exam id int primary key      exam name varchar 50       create table question bank        question id int primary key      question exam id int not null      question text varchar 1024  not null      question point value decimal      constraint question exam id fk        foreign key references exams exam id                 ON DELETE CASCADE

User · Answer

And if you just want to create the constraint on its own  you can use ALTER TABLE  alter table MyTable add constraint MyTable MyColumn FK FOREIGN KEY   MyColumn   references MyOtherTable PKColumn    I wouldn t recommend the syntax mentioned by Sara Chipps for inline creation  just because I would rather name my own constraints

User · Answer

I like AlexCuse s answer  but something you should pay attention to whenever you add a foreign key constraint is how you want updates to the referenced column in a row of the referenced table to be treated  and especially how you want deletes of rows in the referenced table to be treated    If a constraint is created like this   alter table MyTable add constraint MyTable MyColumn FK FOREIGN KEY   MyColumn    references MyOtherTable PKColumn       then updates or deletes in the referenced table will blow up with an error if there is a corresponding row in the referencing table   That might be the behaviour you want  but in my experience  it much more commonly isn t   If you instead create it like this   alter table MyTable add constraint MyTable MyColumn FK FOREIGN KEY   MyColumn    references MyOtherTable PKColumn  on update cascade  on delete cascade     then updates and deletes in the parent table will result in updates and deletes of the corresponding rows in the referencing table    I m not suggesting that the default should be changed  the default errs on the side of caution  which is good  I m just saying it s something that a person who is creating constaints should always pay attention to    This can be done  by the way  when creating a table  like this   create table ProductCategories     Id           int identity primary key    ProductId    int references Products Id                 on update cascade on delete cascade   CategoryId   int references Categories Id                  on update cascade on delete cascade

[sql] How do I create a foreign key in SQL Server?

Examples related to sql

Examples related to sql-server

Examples related to tsql