What are the SQL Constraint types in MySql

Constraints are a way to limit the type of data that can go into a table and can be specified when a table iscreated (with the CREATE TABLE statement)or after the table is created (with the ALTER TABLE tatement). We will focus on the following constraints:

• NOT NULL
• UNIQUE
• PRIMARY KEY
• FOREIGN KEY
• CHECK
• DEFAULT

We will explain and demonstrate the constraint listed above.

NOT NULL

Ensures that a column cannot have NULL value. Example : -when creating a table we can specify that a column cannot receive null values. How do we do that?

CREATE TABLE TEST( NAME varchar(10) not null,EMAIL varchar(10) );

-we have specified that NAME column can not receive null values NULL values What are null values ? NULL means that data does not exist. NULL does not equal to 0 or an empty string.Both 0 and emptystring represents a value, while NULL has no value. -aggregate functions such as SUM, COUNT, AVG, MAX, and MIN exclude NULL values.

UNIQUE

-the unique constraint ensures that there are only distinct values on the same column(no values are repeated). Example : -when creating a table we can specify that the column email will receive only oncea distinct values andthat values cannot be inserted more than once in the samecolumn . How do we do that?

CREATE TABLE TEST( NAME varchar(10) , EMAIL varchar(10) unique  );

PRIMARY KEY

What are the primary keys ? The PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain NULL values. Each table should have a primary key, and each table can have only ONE primary key.

CREATE TABLE TEST(
ID int NOT NULL PRIMARY KEY,
NAME varchar(10) ,
EMAIL varchar(10) unique
);

-one primary key can contain more than one column Example :

CREATE TABLE TEST(
ID int NOT NULL,
NAME varchar(10) ,
EMAIL varchar(10) unique,
Constraint PK_PRIMARY PRIMARY KEY (ID,NAME));

-we see that we give a name to the primary key and then tell which columns will be included. -so now the primary key is made out of two column.

• Primary keys can be specified either when the table is created or after using the alter table command.

Example : -example of primary key creation after the table is created

ALTER TABLE TEST    ADD PRIMARY KEY (ID);

FOREIGN KEY

The a foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words,only values that are supposed to appear in the database are permitted. -as we can see in the image the Table 1 has a primary key (Column 1) that will be a foreign key in Table 2 (Column 2) -the Column 2 from Table 2 will be a reference of Column 1 from Table 1. The Syntax to create a foreign key is : -will use the image as our example

Create the Table 1: Create table Table 1( Column 1 not null Primary key, Column 2);

So we have a primary key created now. Let's create our Table 2 that will hold a Foreign Key referencing out Column 1 from Table 1.

Create table Table 2(
Column 1,
Column 2,
Foreign Key (Column 2) references Table 1(Column 1)
);

-alright this is a bit complex for new comers I know, as it was for me as well at the beginning. So let's do more examples: -I will show the General syntax and explain it :

Create Table_name (
 "Column Name" data type ,
"Another Column Name" data type ,
"Foreign key Holder Column" data type,
Foreign Key ("Foreign key folder") references
Table_Name_of_Primary_Key ("Column of Primary key")
  );

Ok , now we will explain every part of it :

• "Foreign key Holder Column" it will be the column that will reference the "Column of Primary key" found in Table_Name_of_Primary_Key.

Note: - the data type of the Foreign Key Column must be the same as the Primary Key Column data type. Example : -the Column 2 (foreign key) from Table 2 must hold the same data type as Column1(primary key) from Table 1. The foreign key can be added to the table as well after the table was created using the alter table :

Alter table table name Add Foreign Key ("Foreign key folder")
references Table_Name_of_Primary_Key("Column of Primary key");

-we will go more into Foreign Key Details in future tutorials.

CHECK

The CHECK constraint is used to limit the value range that can be placed in a column. CHECK constraint ensures that all values in a column satisfy certain conditions If you define a CHECK constraint on a table it can limit the values in certain columnsbased on values in other columns in the row. CHECK constraint is used to ensure data quality. Create table example with column that hold CHECK constraints Example : Assume we have a site that only receives visitors that have more than 18 years. And we need to create a table to hold them, but we want to make sure that only people with the age more than 18 can register. -so for that we will create a CHECK constraint on the column that will receive the AGE values :

Create table TEST (
Id int,
Age int CHECK (Age  18),Name varchar(20));

Great.

• and now let us try to see the check constraint at work.

insert into test values (1,2,'Mark');

• we see that we will revive an constraint violation error.

-ok now insert a value biggest then 18(which is the value that the CHECK constraint holds).

insert into test values (1,19,'Mark');

-we see that the insert statement worked. Example : -we can also use CHECK with a list of values

Create table TEST (Id int,Age int,City varchar(20) default ('New York','London','Sydney'));

-so using the same criteria the values that we will insert into column CITY mustbe one of the values listed in the DEFAULT values .

DEFAULT

DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value. Example :

Create table TEST (Id int,Name varchar(20),Status char(1) DEFAULT('N'));

Even though we didn't specify a value for the status column there will be a default values that will be inserted on the column every time we insert a row. The default value will be added to all new records, if no other value is specified To create a DEFAULT constraint after the table was created use:

ALTER TABLE table_name   ALTER (column name) DEFAULT 'default value';