What is the SQL FOREIGN KEY Constraint and how it can be used with the database?
The SQL FOREIGN KEY Constraint is required to avoid any operation that can break the link between two tables.
Foregin key in one table is a reference to the primary key in another table.
SQL FOREIGN KEY Constraint Example:
Lets continue with our same example of havin two tables in your database. One is 'order' table and another is 'customer' table. The relation between these two tables is one to many. Where one customer can have many orders but one order belongs to one and only one customer.
Hence, each order must have customer id associated with it. Customer table also needs to have customer id to uniquely identify each customer. We can make customer id as a primary key in customer table. Also, on the order table we can define customer id as a foreign key that references back to the customer table.
Use following syntax of sql foregin key constraint at the time of creating table:
CREATE TABLE customer ( customer_id INT NOT NULL, name VARCHAR(50) NOT NULL, email VARCHAR(50), PRIMARY KEY(order_number) ); CREATE TABLE order( order_number INT NOT NULL, order_total DOUBLE(10,2) default '0.0', customer_id INT NOT NULL, PRIMARY KEY(order_number), FOREGIN KEY (customer_id) REFERENCES customer(customer_id) );