w3resource

MySQL CONSTRAINT

CONSTRAINT

MySQL CONSTRAINT is used to define rules to allow or restrict what values can be stored in columns. The purpose of inducing constraints is to enforce the integrity of a database.

MySQL CONSTRAINTS are used to limit the type of data that can be inserted into a table.

MySQL CONSTRAINTS can be classified into two types - column level and table level.

The column level constraints can apply only to one column where as table level constraints are applied to the entire table.

MySQL CONSTRAINT is declared at the time of creating a table.

MySQL CONSTRAINTs are:

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK
  • DEFAULT
CONSTRAINT DESCRIPTION
NOT NULL In MySQL NOT NULL constraint allows to specify that a column can not contain any NULL value. MySQL NOT NULL can be used to CREATE and ALTER a table.
UNIQUE The UNIQUE constraint in MySQL does not allow to insert a duplicate value in a column. The UNIQUE constraint maintains the uniqueness of a column in a table. More than one UNIQUE column can be used in a table.
PRIMARY KEY A PRIMARY KEY constraint for a table enforces the table to accept unique data for a specific column and this constraint creates a unique index for accessing the table faster.
FOREIGN KEY A FOREIGN KEY in MySQL creates a link between two tables by one specific column of both tables. The specified column in one table must be a PRIMARY KEY and referred by the column of another table known as FOREIGN KEY.
CHECK A CHECK constraint controls the values in the associated column. The CHECK constraint determines whether the value is valid or not from a logical expression.
DEFAULT In a MySQL table, each column must contain a value ( including a NULL). While inserting data into a table, if no value is supplied to a column, then the column gets the value set as DEFAULT.

Syntax:

CREATE  TABLE [table name]                                   
([column name] [data type]([size]) [column constraint]….
[table constraint] ([[column name]……])……); 

Explanation:

The above MySQL code shows how to create a table with some constraints. Constraints are applied to columns as well as tables.

You can replace table name, column name, data type and size with your own.

Contents:

MySQL CREATE TABLE with NULL CONSTRAINT

Using the default value as NOT NULL, while creating a MySQL table, it can be enforced that a column in a table is not allowed to store NULL values.

If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used.

Example:

If you want to create a table 'newauthor' where no columns are allowed to store NULL VALUES the following statement can be used.

CREATE TABLE IF NOT EXISTS newauthor
(aut_id varchar(8) NOT NULL,	   
aut_name varchar(50) NOT NULL,   
country varchar(25) NOT NULL,	   
home_city varchar(25) NOT NULL ); 

Here in the above statement the constraint 'NOT NULL' have been used to exclude the NULL VALUE.

The following picture shows that the columns will not accept the NULL values.

mysql not null constraint

MySQL CREATE TABLE to check values with CHECK CONSTRAINT

Adding a CHECK CONSTRAINT on a column of a table, you can limit the range of values allowed to be stored in that column.

Example:

If you want to create a table 'newbook_mast' with a PRIMARY KEY on 'book _id' column, a unique constraint on 'isbn_no' column and a set the no_page in such, that it would hold values more than zero only, the following statement can be used.

CREATE TABLE IF NOT EXISTS 
newbook_mast (book_id varchar(15) NOT NULL UNIQUE,          
book_name varchar(50)  ,           
isbn_no varchar(15)  NOT NULL UNIQUE  ,           
cate_id varchar(8)  ,             
aut_id varchar(8) ,             
pub_id varchar(8) ,            
dt_of_pub date ,             
pub_lang varchar(15) ,           
no_page decimal(5,0) 
CHECK(no_page>0) ,            
book_price decimal(8,2) ,             
PRIMARY KEY (book_id)               
);

Here in the above MySQL statement will create a table 'newbook_mast' with a PRIMARY KEY on 'book _id' column, unique constraint on 'isbn_no' column and adding CHECK(no_page>0) will set the no_page in such, that it would hold values more than zero only.

MySQL CREATE TABLE with CHECK CONSTRAINT using IN operator

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with IN operator.

Example:

If you want to create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and checking a limit value for the column country are 'USA','UK' and 'India', the following statement can be used.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL , 
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL CHECK (country IN ('USA','UK','India')), 
home_city varchar(25) NOT NULL, 
PRIMARY KEY (aut_id,home_city));

Here in the above MySQL statement will create a table 'newauthor' with a PRIMARY KEY on a combination of two columns (aut_id,home_city) and the value for the column country has been limited by using IN operator.

MySQL CREATE TABLE with CHECK CONSTRAINT and LIKE operator

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with LIKE operator.

Example:

The MySQL statement stated below will create a table 'newbook_mast' with a PRIMARY KEY on 'book_id' column and a CHECK constraint to limit value to be stored for the column dt_of_pub along with LIKE operator and another CHECK constraint to column no_page (without using LIKE operator).

CHECK (dt_of_pub LIKE '--/--/----') checks whether the format of the date to be stored is the column dt_of_pub is like '--/--/----'.

Here is the statement.

CREATE TABLE IF NOT EXISTS newbook_mast
( book_id	varchar(15) NOT NULL UNIQUE, 
book_name varchar(50) , 
isbn_no varchar(15) NOT NULL UNIQUE ,
cate_id varchar(8) , 
aut_id varchar(8) , 
pub_id varchar(8) ,
dt_of_pub date CHECK (dt_of_pub LIKE '--/--/----'), 
pub_lang varchar(15) ,
no_page decimal(5,0) CHECK(no_page>0) ,
book_price decimal(8,2) ,
PRIMARY KEY (book_id) );
 

MySQL CREATE TABLE with AND and OR operator and CHECK CONSTRAINT

MySQL CHECK CONSTRAINT can be applied to a column of a table, to set a limit for storing values within a range, along with AND and OR operator.

Example

The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column and CHECK constraint along with AND and OR operator for country and pub_city columns.

CHECK ((country='India' AND pub_city='Mumbai') OR (country='India' AND pub_city='New Delhi')) checks whether (i)country is INDIA and pub_city is Mumbai OR (ii) country is INDIA and pub_city is New Delhi.

MySQL Manual says "The CHECK clause is parsed but ignored by all storage engines."

Here is the statement.

CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) ,
pub_name varchar(50),
pub_city varchar(25) ,          
country varchar(25) ,
country_office varchar(25) ,
no_of_branch int(3),          
estd date 
CHECK ((country='India' AND pub_city='Mumbai')            
OR (country='India' AND pub_city='New Delhi')) , 
PRIMARY KEY (pub_id) );

MySQL UNIQUE CONSTRAINT

The UNIQUE constraint creates an index such that, all values in the index column must be unique. An error occurs when any body tries to add a new row with a key value that already exists in that row.

Example:

The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which will store unique values only since UNIQUE (aut_id) is used.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50)
NOT NULL,
country varchar(25) NOT NULL,
home_city varchar(25)
NOT NULL, 
UNIQUE (aut_id)); 

The picture below shows the structure of the table.

mysql unique constraint sample1

Example of MySQL UNIQUE CONSTRAINT check unique value

The MySQL statement stated below will create a table 'newauthor' with a column 'aut_id' which is meant to store unique values only. Notice that UNIQUE is used within the column definition

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL UNIQUE ,
aut_name varchar(50) NOT NULL,
country varchar(25) 
NOT NULL,
home_city varchar(25) NOT NULL);

The following picture shows the Structure of the Table

mysql unique constraint sample2

MySQL CREATE TABLE with DEFAULT CONSTRAINT

While creating a table, MySQL allows you assign DEFAULT CONSTRAINTS to columns. DEFAULT is used to set a default value for a column and is applied using DEFAULT default_value; where default_value is the default value set to the column.

Example:

The MySQL statement stated below will create a table 'newpublisher' with a PRIMARY KEY on 'pub_id' column, a CHECK constraint with logical operators for country and pub-city columns and a default value for pub_id, pub_name, pub_city and country columns.

The MySQL statement also sets the default value white space for pub_id, pub_name, pub_city columns and 'India' as a default value for a country column.

Here is the statement below.

CREATE TABLE IF NOT EXISTS newpublisher
(pub_id varchar(8) NOT NULL UNIQUE DEFAULT '' ,           
pub_name varchar(50) NOT NULL  DEFAULT '' ,          
pub_city varchar(25) NOT NULL  DEFAULT '' ,          
country varchar(25) NOT NULL DEFAULT 'India',          
country_office varchar(25) , 
no_of_branch int(3),        
estd date
CHECK ((country='India' AND pub_city='Mumbai')
OR (country='India' AND pub_city='New Delhi')) ,
PRIMARY KEY (pub_id));

MySQL CREATE TABLE with AUTO INCREMENT

MySQL allows you to set AUTO_INCREMENT to a column. Doing so will increase the value of that column by 1 automatically, each time a new record is added.

Example:

The MySQL statement stated below will create a table 'newauthor' with a PRIMARY KEY on 'id' column and the 'id' column is an auto incremented field.

CREATE TABLE IF NOT EXISTS newauthor
(id int NOT NULL AUTO_INCREMENT,
aut_id varchar(8),   
aut_name varchar(50),
country varchar(25),
home_city varchar(25) NOT NULL,
PRIMARY KEY (id));
 

MySQL PRIMARY KEY CONSTRAINT

Usually, a table has a column or combination of columns that contain values used to uniquely identify each row in the table.This column or combination of columns is called PRIMARY KEY and can be created by defining a PRIMARY KEY CONSTRAINT while creating a table. A table can have only one PRIMARY KEY. A PRIMARY KEY column cannot contain NULL values.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the column aut_id.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL ,   
aut_name varchar(50) NOT NULL, 
country varchar(25) NOT NULL,
home_city varchar(25) NOT NULL,         
PRIMARY KEY (aut_id)); 

MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column

In this topic, we have discussed how to set a PRIMARY KEY CONSTRAINT on a column of a table.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY set to the aut_id column. Notice that here PRIMARY KEY keyword is used within the column definition.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL  PRIMARY KEY,	   
aut_name varchar(50) NOT NULL, 
country varchar(25) 
NOT NULL, 
home_city varchar(25) NOT NULL);

MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT

In this topic, we have discussed how to set PRIMARY KEY as well as UNIQUE constraints on columns of a table while creating a table with CREATE TABLE command.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set to the aut_id column and UNIQUE is set to the home_city column.

CREATE TABLE IF NOT EXISTS
newauthor(aut_id varchar(8) NOT NULL  PRIMARY KEY,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL, 
home_city varchar(25) NOT NULL UNIQUE); 

MySQL CREATE TABLE PRIMARY KEY on multiple columns

MySQL allows you to set PRIMARY KEY on multiple columns of a table. Doing this allows you to work on multiple columns as a single entity set as PRIMARY KEY for a table.

Example:

The MySQL statement stated below will create a table 'newauthor' in which PRIMARY KEY is set with the combination of aut_id and home_city columns.

CREATE TABLE IF NOT EXISTS 
newauthor(aut_id varchar(8) NOT NULL ,
aut_name varchar(50) NOT NULL,
country varchar(25) NOT NULL,   
home_city varchar(25) NOT NULL, 
PRIMARY KEY (aut_id, home_city)); 

MySQL creating table with FOREIGN KEY CONSTRAINT

While creating (or modifying) a MySQL table, you can set a FOREIGN KEY CONSTRAINT to a column of the table. A foreign key is a column or combination of columns which can be used to set a link between the data in two tables. PRIMARY KEY of a table is linked to the FOREIGN KEY of another table to enhance data integrity.

Syntax:

FOREIGN KEY [column list] REFERENCES [primary key table] ([column list]);

Arguments:

Name Description
column list A list of the columns on which FOREIGN KEY is to be set.
REFERENCES Keyword.
primary key table Table name which contains the PRIMARY KEY.
column list A list of the columns on which PRIMARY KEY is set in the primary key table.

Example:

If you want to do the following tasks:

A new table 'newbook_mast' will be created.

The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.

The FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.

The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.

The FOREIGN KEY 'aut_id' for the table 'newbook_mast' points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.

That means the 'aut_id's which are present in the 'newauthor' table, only those authors will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newbook_mast 
(book_id varchar(15) NOT NULL PRIMARY KEY,
book_name varchar(50)  ,
isbn_no varchar(15)  NOT NULL  ,
cate_id varchar(8)  , 
aut_id varchar(8) , 
pub_id varchar(8) ,          
dt_of_pub date ,
pub_lang varchar(15) ,
no_page decimal(5,0) ,         
book_price decimal(8,2) ,
FOREIGN KEY (aut_id) REFERENCES newauthor(aut_id));

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple columns

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple columns of a table. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks:

A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'.
That means the distinct ('ord_no' and 'book_id') combination which are present in the in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase', which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,
cate_id varchar(8) ,
receive_qty int(5) ,
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES  neworder(ord_no,book_id),   
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES  category(cate_id));

MySQL CREATE TABLE with FOREIGN KEY CONSTRAINT on multiple tables

MySQL allows assigning FOREIGN KEY CONSTRAINTS on multiple tables. Doing this, more than one columns of a table is set with a FOREIGN KEY CONSTRAINT referenced to PRIMARY KEYs belonging to different tables.

Example:

If you want to do the following tasks :

Aa new table 'newbook_mast' will be created.
The PRIMARY KEY for that table 'newbook_mast' is 'book_id'.
The one FOREIGN KEY for the table 'newbook_mast' is 'aut_id'.
The another FOREIGN KEY for the table 'newbook_mast' is 'pub_id'.
The 'aut_id' is the PRIMARY KEY for the table 'newauthor'.
The 'pub_id' is the PRIMARY KEY for the table 'newpublisher'.
The FOREIGN KEY 'aut_id' for the table 'newbook_mast', which points to the PRIMARY KEY 'aut_id' of the table 'newauthor'.
That means the 'aut_id' which are present in the in the 'nuwauthor' table only those authors will come to the 'newbook_mast' table.
The another FOREIGN KEY 'pub_id' for the table 'newbook_mast' , which points to the PRIMARY KEY 'pub_id' of the table 'newpublisher'.
That means the 'pub_id' which are present in the in the 'newpublisher' table only those publishers will come to the 'newbook_mast' table.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS 
newbook_mast (book_id varchar(15) NOT NULL PRIMARY KEY,          
book_name varchar(50)  , 
isbn_no varchar(15)  NOT NULL  , 
cate_id varchar(8), 
aut_id varchar(8) ,
pub_id varchar(8) , 
dt_of_pub date , 
pub_lang varchar(15) ,           
no_page decimal(5,0) , 
book_price decimal(8,2) ,
INDEX (aut_id), 
FOREIGN KEY(aut_id) REFERENCES newauthor(aut_id), 
INDEX(pub_id),
FOREIGN KEY(pub_id) REFERENCES newpublisher(pub_id) );

MySQL CREATE TABLE with CASCADE and RESTRICT

MySQL allows creating a table with CASCADE and RESTRICT options.

CASCADE option deletes or updates the row from the parent table (containing PRIMARY KEYs), and automatically delete or update the matching rows in the child table (containing FOREIGN KEYs).

RESTRICT option bars the removal (i.e. using delete) or modification (i..e using an update) of rows from the parent table.

Example:

If you want to do the following tasks:

A new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON UPDATE CASCADE ensures that the records inside the child table 'newpurchase' always points to the PRIMARY KEY inside the parent table 'neworder'.
If any record gets deleted/updated from the 'neworder' table MySQL handles the deletion/updating of the records from 'newpurchase' table.
ON DELETE RESTRICT prevents a record in a parent table 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase'.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,     
invoice_dt date ,  
ord_no varchar(25) ,    
ord_date date ,    
receive_dt date ,      
book_id varchar(8) , 
book_name varchar(50) ,
pub_lang varchar(8) , 
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,
total_cost decimal(12,2) , 
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES         
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE RESTRICT,  
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id))

MySQL CREATE TABLE with SET NULL

MySQL allows you to create a table with SET NULL option. Doing so will delete or update the row from the parent table, and set the foreign key column or columns in the child table to NULL.

You can use SET NULL for DELETE as well as UPDATE.

If SET NULL is used, you should not set NOT NULL options to the columns of the child table (containing FOREIGN KEYS).

Example:

If you want to do the following tasks:

Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.

The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
ON UPDATE SET NULL recurses to update the 'newpurchase' table it has already updated during the cascade, it acts like RESTRICT. It prevents infinite loops resulting from cascaded updates.

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS newpurchase
(invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,       
invoice_dt date , 
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,      
book_id varchar(8) ,
book_name varchar(50) ,
pub_lang varchar(8) ,     
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,   
total_cost decimal(12,2) , 
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES neworder
(ord_no,book_id)
ON UPDATE CASCADE ON DELETE 
SET NULL,
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));

MySQL CREATE TABLE with NO ACTION

NO ACTION option in MySQL is equivalent to RESTRICT.

Example:

If you want to do the following tasks:

Aa new table 'newpurchase' will be created.
The PRIMARY KEY for that table 'newpurchase' is 'invoice_no'.
The one FOREIGN KEY for the table 'newpurchase' is a combination of 'ord_no' and 'book_id'.
The another FOREIGN KEY for the table 'newpurchase' is 'cate_id'.
The 'ord_no' and 'book_id' combination is the PRIMARY KEY for the table 'neworder'.
The 'cate_id' is the PRIMARY KEY for the table 'category'.
The FOREIGN KEY 'ord_no' and 'book_id' combination for the table 'newpurchase', which points to the PRIMARY KEY 'ord_no' and 'book_id' combination of the table 'neworder'. That means the distinct ('ord_no' and 'book_id') combination which are present in the 'neworder' table only those unique 'order number' and 'book id' combination will come in the 'newpurchase' table.
The another FOREIGN KEY 'cate_id' for the table 'newpurchase' , which points to the PRIMARY KEY 'cate_id' of the table 'category'. That means the 'cate_id' which are present in the 'category' table only those 'category' will come in the 'newpurchase' table.
The ON DELETE NO ACTION are preventing a record in a parent 'neworder' being deleted or altered when it is still referenced from a child table 'newpurchase' .

Here is the MySQL statement below for the above tasks.

CREATE TABLE IF NOT EXISTS 
newpurchase (invoice_no varchar(12) NOT NULL UNIQUE PRIMARY KEY,        
invoice_dt date ,
ord_no varchar(25) ,
ord_date date ,
receive_dt date ,       
book_id varchar(8) ,
book_name varchar(50) , 
pub_lang varchar(8) ,
cate_id varchar(8) , 
receive_qty int(5) , 
purch_price decimal(12,2) ,
total_cost decimal(12,2) ,
INDEX (ord_no,book_id),
FOREIGN KEY(ord_no,book_id) REFERENCES         
neworder(ord_no,book_id)
ON UPDATE CASCADE ON DELETE NO ACTION,       
INDEX (cate_id),
FOREIGN KEY(cate_id) REFERENCES category(cate_id));

Previous: Loading data into a table and usage of line terminator
Next: MySQL CREATE INDEX



Follow us on Facebook and Twitter for latest update.