Questions And Answers

More Tutorials

SQLServer Foreign Keys

Foreign Keys explained

Foreign Keys constraints ensure data integrity, by enforcing that values in one table must match values in another table.

An example of where a foreign key is required is: In a university, a course must belong to a department. Code for the this scenario is:

CREATE TABLE Department (
 Dept_Code CHAR (5) PRIMARY KEY,
 Dept_Name VARCHAR (20) UNIQUE

Insert values with the following statement:

INSERT INTO Department VALUES ('CS205', 'Computer Science');

The following table will contain the information of the subjects offered by the Computer science branch:

CREATE TABLE Programming_Courses (
 Dept_Code CHAR(5),
 Prg_Name VARCHAR (50) UNIQUE,
 FOREIGN KEY (Dept_Code) References Department(Dept_Code)

(The data type of the Foreign Key must match the datatype of the referenced key.)

The Foreign Key constraint on the column Dept_Code allows values only if they already exist in the referenced table,
Department. This means that if you try to insert the following values:

INSERT INTO Programming_Courses Values ('CS300', 'FDB-DB001', 'Database Systems');

the database will raise a Foreign Key violation error, because CS300 does not exist in the Department table. But when you try a key value that exists:

INSERT INTO Programming_Courses VALUES ('CS205', 'FDB-DB001', 'Database Systems');
INSERT INTO Programming_Courses VALUES ('CS205', 'DB2-DB002', 'Database Systems II');

then the database allows these values.

A few tips for using Foreign Keys

.A Foreign Key must reference a UNIQUE (or PRIMARY) key in the parent table.
.Entering a NULL value in a Foreign Key column does not raise an error.
.Foreign Key constraints can reference tables within the same database.
.Foreign Key constraints can refer to another column in the same table (self-reference)


In this page (written and validated by ) you learned about SQLServer Foreign Keys . What's Next? If you are interested in completing SQLServer tutorial, your next topic will be learning about: SQLServer Sequence.

Incorrect info or code snippet? We take very seriously the accuracy of the information provided on our website. We also make sure to test all snippets and examples provided for each section. If you find any incorrect information, please send us an email about the issue:

Share On:

Mockstacks was launched to help beginners learn programming languages; the site is optimized with no Ads as, Ads might slow down the performance. We also don't track any personal information; we also don't collect any kind of data unless the user provided us a corrected information. Almost all examples have been tested. Tutorials, references, and examples are constantly reviewed to avoid errors, but we cannot warrant full correctness of all content. By using, you agree to have read and accepted our terms of use, cookies and privacy policy.