SQL Basics | DDL: Data Definition Language | Foreign Key

Tags:

• SQL
• Database

A foreign key is used to ensure referential integrity by pointing to the primary key field of one or more other tables. In other words, it ensures that only allowed data values are stored in the database.

Foreign key example

For example, suppose there are two tables. One is the customer table, which records all customer data, and the other is the orders table, which records all customer orders. There is one constraint: every customer in the order data must exist in the customer table. Here, you set a foreign key on the orders table, and that foreign key references the primary key of the customer table. This ensures that every customer in the orders table exists in the customer table. In other words, the orders table contains data for customers that exist in the customer table.

The structure of the two tables is as follows.

customer table

Field name	Property
sid	Primary key
last_name
first_name

orders table

Field name	Property
order_id	Primary key
order_date
customer_sid	Foreign key
amount

In the example above, the customer_sid field in the orders table is a foreign key that points to the sid field in the customer table.

The following are several ways to specify a foreign key when creating the orders table.

MySQL:

CREATE TABLE orders  (
    order_id INTEGER,
    order_date DATE,
    customer_sid INTEGER,
    amount DOUBLE,
    PRIMARY KEY (order_id),
    FOREIGN KEY (customer_sid) REFERENCES customer (sid)
);

Oracle:

CREATE TABLE orders (
    order_id INTEGER PRIMARY KEY,
    order_date DATE,
    customer_sid INTEGER REFERENCES customer (sid),
    amount DOUBLE
);

SQL Server:

CREATE TABLE orders  (
    order_id integer PRIMARY KEY,
    order_date DATETIME,
    customer_sid INTEGER REFERENCES customer(sid),
    amount DOUBLE
);

Next, look at examples of specifying a foreign key by changing the table structure.
Here, assume that the orders table has already been created and no foreign key has been specified.

MySQL:

ALTER TABLE orders
ADD FOREIGN KEY (customer_sid) REFERENCES customer (sid);

Oracle:

ALTER TABLE orders
ADD (CONSTRAINT fk_orders1) FOREIGN KEY (customer_sid) REFERENCES customer(sid);

SQL Server:

ALTER TABLE orders
ADD FOREIGN KEY (customer_sid) REFERENCES customer (sid);