SQL Constraints, Not Null, Unique, Primary Key

Constraints

While creating a table, you can specify constraints also if needed. Constraint is a type pf limitation that you can put on the data entered in the table. Following are the constraints:

NOT NULL	Column does not accept NULL values
CHECK	Limits the values that can be placed in a column
UNIQUE	Values for all the records in the column must be unique
PRIMARY KEY	Identify the column or set of columns whose values uniquely identify a row in a table
FOREIGN KEY	Identify the relationships between tables

NOT NULL

By default, a column can hold NULL value. If we not want to allow NULL or empty value in a column of our table, then we need to place an SQL Constraint on this column specifying that NULL empty column is now not an allowable value.
Example:

CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL,
fname VARCHAR (200), 
lname VARCHAR(200) NOT NULL,
address VARCHAR(500),
city VARCHAR (100),
state VARCHAR (100));

Now we insert data in this table like we had inserted before.

INSERT INTO Emp_Add
(ssn, fname, address, city, state)
VALUES (202354897, 'Svendson', '82 Broadway', 'Bayonne', 'New Jersey');

The above record will not be saved in the Emp_Add table, since the value of lname is null in the above query.

CHECK

By using the CHECK constraint, a limit can be placed on the value being entered in a column.
Example:

CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL,
fname VARCHAR (200),
lname VARCHAR (200),
address VARCHAR (500),
city VARCHAR (100),
state VARCHAR (100),
CHECK (state!='New Jersey'));

Now we insert data in the Emp_Add table.

INSERT INTO Emp_Add
(ssn, fname, address, city, state)
VALUES (202354897, 'Svendson', '82 Broadway', 'Bayonne', 'New Jersey');

The above record will not be added. Another way to use CHECK statement is to define it. Multiple CHECK constraints can be used.
Example:

CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL,
fname VARCHAR (200),
lname VARCHAR (200),
address VARCHAR (500),
city VARCHAR (100),
state VARCHAR (100),
CONSTRAINT chk CHECK (state!='New Jersey' AND ssn>0));

To add a CHECK constraint after a table is created,

ALTER TABLE Emp_Add
ADD CHECK (state!='New Jersey' AND ssn>0);

Likewise, to remove a CHECK constraint from a table,

ALTER TABLE Emp_Add
DROP CONSTRAINT chk

UNIQUE

The UNIQUE constraint ensures the uniqueness of the column. It may be considered similar to PRIMARY KEY but a PRIMARY KEY constraint automatically has a UNIQUE constraint defined in it. A table can have as many UNIQUE constraints as need be but it can have only one PRIMARY KEY.
Example:

CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL UNIQUE,
fname VARCHAR (200),
lname VARCHAR(200) NOT NULL,
address VARCHAR(500),
city VARCHAR (100),
state VARCHAR (100));

In the above example, ssn has UNIQUE constraint. None of the rows can have same ssn value.

PRIMARY KEY

The PRIMARY KEY constraint requires each record to be unique. If you want to know how this constraint works see Primary Key

FOREIGN KEY

FOREIGN KEY is used to connect two or more tables in a database. A FOREIGN KEY in one table points to a PRIMARY KEY in another table.
Example:

CREATE TABLE Emp_Add
(ssn INTEGER NOT NULL PRIMARY KEY,
fname VARCHAR (200),
lname VARCHAR(200) NOT NULL,
address VARCHAR(500),
city VARCHAR (100),
state VARCHAR (100));

CREATE TABLE Emp_skill
(ssn INTEGER NOT NULL,
skill VARCHAR (100),
id INTEGER NOT NULL PRIMARY KEY);

The above two tables are linked to each other via ssn. Where ssn is primary key for Emp_Add, it is foreign key to Emp_skill. You can also define FOREIGN KEY on multiple columns just like CHECK constraint. It can also be added or deleted by using ALTER statement.