K-Learn

SQL Foreign Key

Posted on Sunday, 05 March 2023 12:44 PM

A foreign key in SQL is a column or set of columns in a table that refers to the primary key of another table. It is used to create a relationship between two tables, allowing data to be retrieved and manipulated from both tables at once.

Here's an example SQL script to create the tables with primary and foreign keys:

-- Create the student table
CREATE TABLE student (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  email VARCHAR(50) UNIQUE NOT NULL,
  phone VARCHAR(20),
  address VARCHAR(100)
);
-- Create the subject table
CREATE TABLE subject (
  id INT PRIMARY KEY,
  name VARCHAR(50) NOT NULL,
  credits INT NOT NULL
);
-- Create the exam table
CREATE TABLE exam (
  id INT PRIMARY KEY,
  student_id INT NOT NULL,
  subject_id INT NOT NULL,
  marks INT NOT NULL,
  FOREIGN KEY (student_id) REFERENCES student(id),
  FOREIGN KEY (subject_id) REFERENCES subject(id)
);

OUTPUT

In this example,

The student table has a primary key of id, which is also referenced as a foreign key in the exam table. Similarly, the subject table has a primary key of id, which is also referenced as a foreign key in the exam table. The foreign key constraints ensure that the student_id and subject_id columns in the exam table must correspond to valid id values in the student and subject tables, respectively.