SQL: Foreign key constraint

 

Foreign key is used to establish relationship between two tables. For example, you have two tables.

a.   customer: Has basic customer details like name, email_id

b.   customer_orders: Track the orders of customers.

 

customer

customer_id	name	email_id
1	Gopi	gopi@abcdef.com
2	Jaideep	jaideep@abcdef.com
3	Nisya	nisya@abcdef.com

 

customer_orders

order_id	order_code	discount_amount	cust_id
1	LAP-123	231	3
2	OnePlu-9	789	1

 

order_id ‘1’ specifies that customer 3 (Nisya) ordered the item LAP-123 with discount amount of 231.

 

To maintain data consistency, we should make sure that cust_id column value always one of the customer_id presented in customer table. We can apply this constraint with foreign key.

 

How to specify this foreign key constraint?

CONSTRAINT fk_fav_orders_cust_id FOREIGN KEY (cust_id) REFERENCES customer(customer_id)

 

Let’s create tables by executing following statements.

 

CREATE TABLE customer (

    customer_id int AUTO_INCREMENT,

    name varchar(20) NOT NULL,

    email_id varchar(20) NOT NULL,

    PRIMARY KEY(customer_id)

);

 

CREATE TABLE customer_orders (

    order_id int AUTO_INCREMENT,

    order_code varchar(20) NOT NULL,

    discount int NOT NULL,

    cust_id int,

    PRIMARY KEY(order_id),

    CONSTRAINT fk_fav_orders_cust_id FOREIGN KEY (cust_id) REFERENCES customer(customer_id)

); 

mysql> CREATE TABLE customer (
    ->     customer_id int AUTO_INCREMENT,
    ->     name varchar(20) NOT NULL,
    ->     email_id varchar(20) NOT NULL,
    ->     PRIMARY KEY(customer_id)
    -> );
Query OK, 0 rows affected (0.02 sec)

mysql> 
mysql> CREATE TABLE customer_orders (
    ->     order_id int AUTO_INCREMENT,
    ->     order_code varchar(20) NOT NULL,
    ->     discount int NOT NULL,
    ->     cust_id int,
    ->     PRIMARY KEY(order_id),
    ->     CONSTRAINT fk_fav_orders_cust_id FOREIGN KEY (cust_id) REFERENCES customer(customer_id)
    -> );
Query OK, 0 rows affected (0.01 sec)

mysql> 
mysql> desc customer;
+-------------+-------------+------+-----+---------+----------------+
| Field       | Type        | Null | Key | Default | Extra          |
+-------------+-------------+------+-----+---------+----------------+
| customer_id | int         | NO   | PRI | NULL    | auto_increment |
| name        | varchar(20) | NO   |     | NULL    |                |
| email_id    | varchar(20) | NO   |     | NULL    |                |
+-------------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> 
mysql> desc customer_orders;
+------------+-------------+------+-----+---------+----------------+
| Field      | Type        | Null | Key | Default | Extra          |
+------------+-------------+------+-----+---------+----------------+
| order_id   | int         | NO   | PRI | NULL    | auto_increment |
| order_code | varchar(20) | NO   |     | NULL    |                |
| discount   | int         | NO   |     | NULL    |                |
| cust_id    | int         | YES  | MUL | NULL    |                |
+------------+-------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

 

Insert some data into customer table

INSERT INTO customer(name, email_id) VALUES ('Gopi', 'gopi@abcdef.com');

INSERT INTO customer(name, email_id) VALUES ('Jaideep', 'jaideep@abcdef.com');

INSERT INTO customer(name, email_id) VALUES ('Nisya', 'nisya@abcdef.com');

 

Insert some data into customer_order table

INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('LAP-123', 231, 3);

INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('OnePlu-9', 789, 1);

mysql> INSERT INTO customer(name, email_id) VALUES ('Gopi', 'gopi@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer(name, email_id) VALUES ('Jaideep', 'jaideep@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer(name, email_id) VALUES ('Nisya', 'nisya@abcdef.com');
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('LAP-123', 231, 3);
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('OnePlu-9', 789, 1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT * FROM customer;
+-------------+---------+--------------------+
| customer_id | name    | email_id           |
+-------------+---------+--------------------+
|           1 | Gopi    | gopi@abcdef.com    |
|           2 | Jaideep | jaideep@abcdef.com |
|           3 | Nisya   | nisya@abcdef.com   |
+-------------+---------+--------------------+
3 rows in set (0.00 sec)

mysql> 
mysql> SELECT * FROM customer_orders;
+----------+------------+----------+---------+
| order_id | order_code | discount | cust_id |
+----------+------------+----------+---------+
|        1 | LAP-123    |      231 |       3 |
|        2 | OnePlu-9   |      789 |       1 |
+----------+------------+----------+---------+
2 rows in set (0.00 sec)

 

What if I try to insert a cust_id, which is not present in customer table?

Foreign key constraint fails and SQL throws an error.

 

mysql> INSERT INTO customer_orders(order_code, discount, cust_id) VALUES ('OnePlu-9', 789, 111);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`demo`.`customer_orders`, CONSTRAINT `fk_fav_orders_cust_id` FOREIGN KEY (`cust_id`) REFERENCES `customer` (`customer_id`))

 

Previous                                                    Next                                                    Home