SQL FOREIGN KEY Constraint


A SQL FOREIGN KEY is a reference key used to link or point two tables together. The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

A foreign key is a single or multiple fields that references a field (most often the primary key) of another table.

The SQL FOREIGN KEY constraint also prevents unknown or invalid data from being inserted into the foreign key field, because it has to be one of the values contained in the primary key table it points to.

For example, Here we have two tables, a AUTHOR table that includes all information about author data, and an BOOKS table that includes all books information as well as author data.

Here the main important thing is, all books(created as foreign key column in the books table) must be connected or associated with a author information that is already in the AUTHOR table. So we create a foreign key on the BOOKS table and have it reference or link the primary key of the AUTHOR table.

Now here the BOOKS table cannot contain data on a author that is not available in the AUTHOR table.

Let's illustrate the foreign key with an example. Look at the following two tables:

The "Author" table:

AuthorId AuthorName City
1 Suresh Babu Nasik
2 Siva Kumar Chennai
3 Bala Haris Varshith Madurai

The "Books" table:

BookId AuthorId BookName Description
101 1 Sql Complete Reference It descripes how to write and execute SQL statement into database.
102 2 Sql Commands It exaplins a list of SQL command.
103 1 Pl Sql Quick Programming How to write and execute SQL programming using pl sql.
104 3 Sql Query Injection How to hack SQL queries in database.
105 3 The Power Of Pl Sql It descripes power of pl SQL programming.

Search Keys

  • sql foreign key constraint
  • foreign key
  • foreign key sql server
  • foreign key in dbms
  • foreign key definition
  • drop foreign key
  • difference between primary key and foreign key
  • alter table foreign key
  • alter table add foreign key
  • primary key foreign key
  • foreign key example
  • sql server foreign key
  • foreign key constraint
  • delete foreign key
  • remove foreign key constraint
  • remove foreign key
  • foreign key on update
  • add foreign key oracle
  • foreign key create table
  • oracle add constraint foreign key

SQL FOREIGN KEY Constraint on CREATE TABLE

The following SQL statement creates a SQL PRIMARY KEY constraint on the "BookID" field when the "Books" table is created:

For MySql


CREATE TABLE Author
(
AuthorId INT NOT NULL,
AuthorName VARCHAR(255),
City VARCHAR(255),
PRIMARY KEY (AuthorId)
);

CREATE TABLE Books
(
BookId INT NOT NULL,
AuthorId INT NOT NULL,
BookName VARCHAR(255),
Description VARCHAR(255),
PRIMARY KEY (BookId),
FOREIGN KEY (AuthorId) REFERENCES Author(AuthorId)
);

For Microsoft SQL Server / Oracle / Microsoft Access


CREATE TABLE Author
(
AuthorId INT NOT NULL PRIMARY KEY,
AuthorName VARCHAR(255),
City VARCHAR(255),
PRIMARY KEY (AuthorId)
);

CREATE TABLE Books
(
BookId INT NOT NULL PRIMARY KEY,
AuthorId INT FOREIGN KEY REFERENCES Author(AuthorId)
BookName VARCHAR(255),
Description VARCHAR(255)
);

In the above example, the following SQL statement creates two new table called Author(contains primary key) and Books(contains foreign key).

Note that the "AuthorId" field in the "Books" table link to the "AuthorId" field in the "Author" table.

The "AuthorId" field in the "Author" table is the PRIMARY KEY in the "Author" table.

The "AuthorId" field in the "Books" table is a FOREIGN KEY in the "Author" table.

The following examples are operations that violate the referential integrity of this table relationship:

  • Inserting a record in the BOOKS table where AuthorId(from Books table) does not appear in the AuthorId(from Author table) field in the AUTHOR table.
  • Deleting a record from the AUTHOR table where the AuthorId(from Author table) of the record to be delete is still present in the AuthorId(from Books table) field in the BOOKS table.

When these actions are attempted, the database would return an error message is stating that referential integrity is violated.

To allow naming of a SQL PRIMARY KEY constraint, and for defining a SQL PRIMARY KEY constraint on multiple fields, use the following SQL syntax:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


CREATE TABLE Books
(
BookID INT NOT NULL,
AuthorID INT NOT NULL,
BookName VARCHAR(255) NOT NULL,
Description VARCHAR(255),
PRIMARY KEY (BookID),
CONSTRAINT fk_BookAuthor FOREIGN KEY (AuthorID)
REFERENCES Author(AuthorID)
);


SQL FOREIGN KEY Constraint on ALTER TABLE

To create a SQL FOREIGN KEY constraint on the "AuthorID" field when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access / MySql


ALTER TABLE Books
ADD FOREIGN KEY (AuthorId)
REFERENCES Author(AuthorId);

To create a SQL FOREIGN KEY constraint on multiple fileds when the table is already created, use the following SQL statement:

For Microsoft SQL Server / Oracle / Microsoft Access


ALTER TABLE Books
ADD CONSTRAINT fk_BooksAuthor
FOREIGN KEY (AuthorId)
REFERENCES Author(AuthorId);


To DROP a SQL FOREIGN KEY Constraint

To drop or remove a SQL FOREIGN KEY constraint, use the following SQL statement:

For MySQL


ALTER TABLE Books
DROP FOREIGN KEY fk_BooksAuthor;

For Microsoft SQL SERVER / Microsoft ACCESS / ORACLE


ALTER TABLE Books
DROP CONSTRAINT fk_BooksAuthor;


Search Keys

  • sql server drop foreign key constraint
  • null foreign key
  • sql constraint foreign key
  • can foreign key be null
  • sql foreign key example
  • foreign key relationship
  • foreign key references
  • set foreign key checks
  • sql insert foreign key
  • primary key and foreign key examples
  • foreign key sql command
  • mssql add foreign key
  • sql add constraint foreign key
  • insert foreign key
  • foreign key table
  • create table with primary key and foreign key
  • sql server foreign key performance
  • sql references foreign key
  • relationship between primary key and foreign key
  • constraint foreign key sql server