How do I create a foreign key in SQL Server?

mmattax_1638 0
Viewed 466911

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?

tsql sql-server sql
11 Answers

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.

AlexCuse_7940 
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_28470

You can also name your foreign key constraint by using:

CONSTRAINT your_name_here FOREIGN KEY (question_exam_id) REFERENCES EXAMS (exam_id)
Sara_Chipps_41400

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

vitorsilva_18428640

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_60432240
Related