Page 82 - SQL

Chapter 24: Foreign Keys

Examples

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 super hero:

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.

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)
);

https://riptutorial.com/ 64