Sql Foreign Keys

Sql Foreign Keys
Nikhil
Published on 2021-08-15 06:12:02

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_Code CHAR(9) PRIMARY KEY,
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).

Creating a table with a foreign key

In this example, we have an existing table, SuperHeros.

This table contains a primary key ID.

We will add a new table in order to store the powers of each superhero:

CREATE TABLE HeroPowers
(
ID int NOT NULL PRIMARY KEY,
Name nvarchar(MAX) NOT NULL,
HeroId int REFERENCES SuperHeros(ID)
)

The column HeroId is a foreign key to the table SuperHeros.

ATutorialHub Related Guide

Comments (8)

Leave a Comment

Your email address will not be published. Required fields are marked*

User Comments

html tutorial comments

panduranga gupta

2021-07-05 07:03:13

good website for learning and help me a lot

html tutorial comments

raju

2021-09-25 14:58:47

The awsome website i am looking like for a long time, good work atutorialhub team keep doing

html tutorial comments

Shivani

2021-09-01 15:03:56

Learning a lot from the courses present on atutorialhub. The courses are very well explained. Great experience

html tutorial comments

Harshitha

2021-09-10 15:05:45

It is very helpful to students and easy to learn the concepts

html tutorial comments

Sowmya

2021-09-14 15:06:41

Great job Tutorials are easy to understand Please make use of it

html tutorial comments

Zain Khan

2021-09-18 15:07:23

Great content and customized courses.

html tutorial comments

Rudrakshi Bhatt

2021-09-09 15:08:10

Well structured coursed and explained really well!

html tutorial comments

Pavana Somashekar

2021-09-11 15:09:08

Good platform for beginners and learn a lot on this website