FOREIGN KEY

MySQL

Example

CREATE TABLE Authors (
    ID INT PRIMARY KEY,
    Name VARCHAR(100)
);

CREATE TABLE Books (
    ID INT PRIMARY KEY,
    Title VARCHAR(100),
    AuthorID INT,
    FOREIGN KEY (AuthorID) REFERENCES Authors(ID)
);

Output

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.02 sec)

Explanation

This code excerpt creates two tables, Authors and Books. The Authors table has two columns: ID and Name. The Books table has three columns: ID, Title, and AuthorID. Here AuthorID is declared as a foreign key referencing ID field in the Authors table. This set up ensures each book is associated with an author from the Authors table.

PostgreSQL

Example

CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

Output

Query returned successfully with no result in 500 ms.

Explanation

In the example above, a FOREIGN KEY constraint is defined on the PersonID column in the Orders table. The PersonID column in the Orders table points to the PersonID column in the Persons table. This means that for every entry in the Orders table, the value of PersonID must match an existing value in the PersonID column of the Persons table. With this constraint, you ensure the referential integrity of your data in the PostgreSQL database.

SQL Server

Example

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderName varchar(255) NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Output

Command(s) completed successfully.

Explanation

In the given example, a table named Orders is created. The FOREIGN KEY constraint is utilized on the CustomerID column in the Orders table, pointing to the CustomerID in the Customers table. This action ensures that every value in the Orders.CustomerID column exists in the Customers.CustomerID column, enforcing referential integrity in the database. The output message Command(s) completed successfully indicates that the table was successfully created with the foreign key constraint.

Oracle

Example

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    CustomerID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Output

Table ORDERS created.

Explanation

In this example, a database table named Orders has been created. The FOREIGN KEY constraint on the CustomerID field ensures that every CustomerID in the Orders table matches a CustomerID in the Customers table. This prevents misspelled or nonexistent customer IDs from being added to the Orders table.

SQLite

Example

CREATE TABLE Authors (
    AuthorID INTEGER PRIMARY KEY,
    AuthorName TEXT NOT NULL
);

CREATE TABLE Books (
    BookID INTEGER PRIMARY KEY,
    BookTitle TEXT NOT NULL,
    AuthorID INTEGER,
    FOREIGN KEY(AuthorID) REFERENCES Authors(AuthorID)
);

INSERT INTO Authors (AuthorID, AuthorName) VALUES (1, 'John Doe');
INSERT INTO Books (BookID, BookTitle, AuthorID) VALUES (1, 'Sample Book', 1);

Output

sqlite> .tables
Authors  Books

Explanation

The FOREIGN KEY keyword is used in SQLite (and other SQL databases) to ensure referential integrity of data within your database. In the code example, each book entry in the Books table has an AuthorID that must correspond to an existing AuthorID in the Authors table. This link between tables helps ensure that a book cannot be entered into the database without a referenced author.