What is Foreign Key Constraint in MySQL and How to create Foreign Key in MySQL - MySQL Developer Tutorial

What is Foreign Key Constraint in MySQL and How to create Foreign Key in MySQL

Foreign Key in a table is a column or group of columns that provides a link between data in two tables. The Foreign Key in a table points to the primary key in another table.

Let's create Customer Table with Primary Key by using below DDL statement

CREATE TABLE Customer (
    Customerid INT(11) NOT NULL PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    );

As you can see that Customerid is the primary key in Customer Table.

As we are ready with our first table and have the Primary Key, We are ready to create second table with foreign Key Constraint.

CREATE TABLE Orders (
    OrderId INT (11) Not Null 
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customerid int(11) not null
,FOREIGN KEY (Customerid) REFERENCES Customer(Customerid)
    );

You can see that we have added Customerid column in Orders table that references to Customerid in Customer Table.

The primary key in first table is Customerid and Foreign Key Column in second table is Customer_id, that means the columns don't have to have same names. It is good idea to keep the same name so when you write queries and join the tables, you don't have to take a look which is primary key and which is foreign key, instead you would know that the same column names are in relationship.

Let's insert couple of records and see how Foreign Key Constraint works.

    insert into Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00');

    insert into Orders
    (Orderid,OrderItemName,OrderItemAmt,CustomerId)
    values (1,'TV',1,1);

Both records will be inserted just fine, as we are inserting same customerid in Orders that does exists in Customer.

If we will try to insert any value in Orders that is not present in Customer(CustomerId), It will through an error due to foreign key constraint.

    insert into Orders
    (Orderid,OrderItemName,OrderItemAmt,CustomerId)
    values (1,'TV',1,2);

When we run above query, it through error as we don't have 2 as Customerid in Customer table.

insert into Orders     (Orderid,OrderItemName,OrderItemAmt,CustomerId)     values (1,'TV',1,2) Error Code: 1452. Cannot add or update a child row: a foreign key constraint fails (`techbrothers`.`orders`, CONSTRAINT `orders_ibfk_1` FOREIGN KEY (`Customerid`) REFERENCES `customer` (`customerid`))