SQL Constraints

Not Null

If we specify a field in a table to be NOT NULL, then the field will never accept a null value. You will be not allowed to insert a new row in the table without specifying a value to this field.

CREATE TABLE Student (
  ID int(6) NOT NULL,
  NAME varchar(10) NOT NULL,
  ADDRESS varchar(20)
);

Unique

This constraint helps to uniquely identify each row in the table. i.e. for a particular column, all the rows should have unique values. We can have more than one UNIQUE column in a table.

CREATE TABLE Student (
  ID int(6) NOT NULL UNIQUE,
  NAME varchar(10),
  ADDRESS varchar(20)
);

Primary Key

Primary Key is a field which uniquely identifies each row in the table. If a field in a table is the primary key, then the field will not be able to contain NULL values as well as all the rows should have unique values for this field. So, in other words, we can say that this is a combination of NOT NULL and UNIQUE constraints.
A table can have only one field as the primary key.

CREATE TABLE Student (
  ID int(6) NOT NULL UNIQUE,
  NAME varchar(10),
  ADDRESS varchar(20),
  PRIMARY KEY(ID)
);

Foreign Key

Foreign Key is a field in a table which uniquely identifies each row of another table. This field points to the primary key of another table and creates a link between the tables.

CREATE TABLE Orders (
  O_ID int NOT NULL,
  ORDER_NO int NOT NULL,
  C_ID int,
  PRIMARY KEY (O_ID),
  FOREIGN KEY (C_ID) REFERENCES Customers(C_ID)
);

Check

Using the CHECK constraint we can specify a condition for a field, which should be satisfied at the time of entering values for this field.

CREATE TABLE Student (
  ID int(6) NOT NULL,
  NAME varchar(10) NOT NULL,
  AGE int NOT NULL CHECK (AGE >= 18)
);

Default

This constraint is used to provide a default value for the fields. If at the time of entering new records in the table, the user does not specify any value for these fields then the default value will be assigned to them.

CREATE TABLE Student (
  ID int(6) NOT NULL,
  NAME varchar(10) NOT NULL,
  AGE int DEFAULT 18
);

SaladFingers