Primary Key

Creating Primary key while Creating the table itself

CREATE TABLE Employees (
    ID INT PRIMARY KEY,
    NAME TEXT,
    AGE INT,
    ADDRESS CHAR(50)
);

To add the constraint Primary key to an already existing column in the table

ALTER TABLE Employees
ADD PRIMARY KEY (ID);

Composite Primary Key
We can also use multiple columns to define a primary key. Such key is known as composite key.

CREATE TABLE Customers (
    CustomerID INT,
    StoreID INT,
    CONSTRAINT pk_CustomerID_StoreID PRIMARY KEY (CustomerID,StoreID)

Foreign Key

While creating a table
MySQL

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);

SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:

MySQL / SQL Server / Oracle / MS Access:

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

SQL FOREIGN KEY on ALTER TABLE

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE child_table
ADD FOREIGN KEY (fk_column)
REFERENCES parent_table (parent_key_column)

DROP a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:

ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;

SQL Server / Oracle / MS Access:

ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;

UIQUE

While creating table

CREATE TABLE table_name (
    column1 data_type UNIQUE,
    column2 data_type,
    column3 data_type,
   ....
)

Adding a Unique Constraint to an Existing Table

ALTER TABLE table_name
ADD UNIQUE (column1, column2, ...);

Dropping a Unique Constraint

ALTER TABLE table_name
DROP CONSTRAINT constraint_name;

NOT NULL

Creating a table with NOT NULL

While creating a table

CREATE TABLE Employees (
    ID int NOT NULL,
    Name varchar(255) NOT NULL,
    Age int,
    Address varchar(255)
);

Adding NOT NULL to an existing table

ALTER TABLE Employees
MODIFY Address varchar(255) NOT NULL;

To remove
undeclared attributes as NOT NULL will go back to default settings which is null in this case

alter table products
alter column brand_id smallint;

CHECK

CREATE TABLE table_name (
    column1 datatype CONSTRAINT constraint_name CHECK (condition),
    column2 datatype,
    ...
);

If you need to apply the CHECK constraint on multiple columns, use the following syntax:

CREATE TABLE table_name (
    column1 datatype,
    column2 datatype,
    ...,
    CONSTRAINT constraint_name CHECK (condition)
);

EXAMPLES

CREATE TABLE Employees (
    ID int NOT NULL,
    Age int,
    Salary int CHECK (Salary>0),
);

Multiple Columns

CREATE TABLE Employees (
    ID int NOT NULL,
    Age int,
    Salary int,
    CONSTRAINT CHK_Person CHECK (Age>=18 AND Salary>=0)
);

Alter table

ALTER TABLE Employees
ADD CONSTRAINT CHK_EmployeeAge CHECK (Age >= 21 AND Age <= 60);

SQL - Constraints