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..
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.
MySQL CREATE TABLE with NOT NULL
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)NOTNULL,
aut_name varchar(50)NOTNULL,
country varchar(25)NOTNULL,
home_city varchar(25)NOTNULL);
The following picture shows that the columns will not accept the NULL values.
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.
CREATETABLEIFNOTEXISTS
newbook_mast(book_idvarchar(15)NOTNULLUNIQUE,
book_namevarchar(50),
isbn_novarchar(15)NOTNULLUNIQUE,
cate_idvarchar(8),
aut_idvarchar(8),
pub_idvarchar(8),
dt_of_pubdate,
pub_langvarchar(15),
no_pagedecimal(5,0)
CHECK(no_page>0),
book_pricedecimal(8,2),
PRIMARYKEY(book_id)
);
MySQL UNIQUE CONSTRAINT
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULL,
aut_namevarchar(50)
NOTNULL,
countryvarchar(25)NOTNULL,
home_cityvarchar(25)
NOTNULL,
UNIQUE(aut_id));
Example of MySQL UNIQUE CONSTRAINT check unique value
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULLUNIQUE,
aut_namevarchar(50)NOTNULL,
countryvarchar(25)
NOTNULL,
home_cityvarchar(25)NOTNULL);
MySQL CREATE TABLE with DEFAULT CONSTRAINT
CREATETABLEIFNOTEXISTSnewpublisher
(pub_idvarchar(8)NOTNULLUNIQUEDEFAULT'',
pub_namevarchar(50)NOTNULLDEFAULT'',
pub_cityvarchar(25)NOTNULLDEFAULT'',
countryvarchar(25)NOTNULLDEFAULT'India',
country_officevarchar(25),
no_of_branchint(3),
estddate
CHECK((country='India'ANDpub_city='Mumbai')
OR(country='India'ANDpub_city='New Delhi')),
PRIMARYKEY(pub_id));
MySQL PRIMARY KEY CONSTRAINT
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULL,
aut_namevarchar(50)NOTNULL,
countryvarchar(25)NOTNULL,
home_cityvarchar(25)NOTNULL,
PRIMARYKEY(aut_id));
MySQL CREATE TABLE PRIMARY KEY CONSTRAINT on single column
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULLPRIMARYKEY,
aut_namevarchar(50)NOTNULL,
countryvarchar(25)NOTNULL,
home_cityvarchar(25)NOTNULL);
MySQL CREATE TABLE PRIMARY KEY UNIQUE CONSTRAINT
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULLPRIMARYKEY,
aut_namevarchar(50)NOTNULL,
countryvarchar(25)NOTNULL,
home_cityvarchar(25)NOTNULLUNIQUE);
MySQL CREATE TABLE PRIMARY KEY on multiple columns
CREATETABLEIFNOTEXISTS
newauthor(aut_idvarchar(8)NOTNULL,
aut_namevarchar(50)NOTNULL,
countryvarchar(25)NOTNULL,
home_cityvarchar(25)NOTNULL,
PRIMARYKEY(aut_id,home_city));
MySQL creating table with FOREIGN KEY CONSTRAINT
Syntax :
FOREIGN KEY [column list] REFERENCES [Table name] ([column list]);
Example
CREATETABLEIFNOTEXISTSnewbook_mast
(book_idvarchar(15)NOTNULLPRIMARYKEY,
book_namevarchar(50),
isbn_novarchar(15)NOTNULL,
cate_idvarchar(8),
aut_idvarchar(8),
pub_idvarchar(8),
dt_of_pubdate,
pub_langvarchar(15),
no_pagedecimal(5,0),
book_pricedecimal(8,2),
FOREIGNKEY(aut_id)REFERENCESnewauthor(aut_id));
CREATETABLEIFNOTEXISTSnewpurchase
(invoice_novarchar(12)NOTNULLUNIQUEPRIMARYKEY,
invoice_dtdate,
ord_novarchar(25),
ord_datedate,
receive_dtdate,
book_idvarchar(8),
book_namevarchar(50),
pub_langvarchar(8),
cate_idvarchar(8),
receive_qtyint(5),
purch_pricedecimal(12,2),
total_costdecimal(12,2),
INDEX(ord_no,book_id),
FOREIGNKEY(ord_no,book_id)REFERENCESneworder(ord_no,book_id),
INDEX(cate_id),
FOREIGNKEY(cate_id)REFERENCES category(cate_id));
CREATETABLEIFNOTEXISTS
newbook_mast(book_idvarchar(15)NOTNULLPRIMARYKEY,
book_namevarchar(50),
isbn_novarchar(15)NOTNULL,
cate_idvarchar(8),
aut_idvarchar(8),
pub_idvarchar(8),
dt_of_pubdate,
pub_langvarchar(15),
no_pagedecimal(5,0),
book_pricedecimal(8,2),
INDEX(aut_id),
FOREIGNKEY(aut_id)REFERENCESnewauthor(aut_id),
INDEX(pub_id),
FOREIGNKEY(pub_id)REFERENCESnewpublisher(pub_id));
CREATETABLEIFNOTEXISTSnewpurchase
(invoice_novarchar(12)NOTNULLUNIQUEPRIMARYKEY,
invoice_dtdate,
ord_novarchar(25),
ord_datedate,
receive_dtdate,
book_idvarchar(8),
book_namevarchar(50),
pub_langvarchar(8),
cate_idvarchar(8),
receive_qtyint(5),
purch_pricedecimal(12,2),
total_costdecimal(12,2),
INDEX(ord_no,book_id),
FOREIGNKEY(ord_no,book_id)REFERENCES
neworder(ord_no,book_id)
ONUPDATECASCADEONDELETERESTRICT,
INDEX(cate_id),
FOREIGNKEY(cate_id)REFERENCES category(cate_id))
Here is the MySQL statement below for the above tasks.
CREATETABLEIFNOTEXISTSnewpurchase
(invoice_novarchar(12)NOTNULLUNIQUEPRIMARYKEY,
invoice_dtdate,
ord_novarchar(25),
ord_datedate,
receive_dtdate,
book_idvarchar(8),
book_namevarchar(50),
pub_langvarchar(8),
cate_idvarchar(8),
receive_qtyint(5),
purch_pricedecimal(12,2),
total_costdecimal(12,2),
INDEX(ord_no,book_id),
FOREIGNKEY(ord_no,book_id)REFERENCESneworder
(ord_no,book_id)
ONUPDATECASCADEONDELETE
SETNULL,
INDEX(cate_id),
FOREIGNKEY(cate_id)REFERENCES category(cate_id));
Copy
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.
CREATETABLEIFNOTEXISTS
newpurchase(invoice_novarchar(12)NOTNULLUNIQUEPRIMARYKEY,
invoice_dtdate,
ord_novarchar(25),
ord_datedate,
receive_dtdate,
book_idvarchar(8),
book_namevarchar(50),
pub_langvarchar(8),
cate_idvarchar(8),
receive_qtyint(5),
purch_pricedecimal(12,2),
total_costdecimal(12,2),
INDEX(ord_no,book_id),
FOREIGNKEY(ord_no,book_id)REFERENCES
neworder(ord_no,book_id)
ONUPDATECASCADEONDELETENOACTION,
INDEX(cate_id),
FOREIGNKEY(cate_id)REFERENCES category(cate_id));
