Constraints are used to limit the type of data that can go into a table. Constraints can be column level or table level.

Query Description Example
NOT NULL Column cannot have NULL values. CREATE TABLE myTable (ID int NOT NULL); ALTER TABLE myTable MODIFY ID int NOT NULL;
UNIQUE All values in a column are unique.
PRIMARY KEY Uniquely identifies each row in a table (NOT NULL and UNIQUE).
FOREIGN KEY Uniquely identifies a row/record in another table.
CHECK Check if all values in a column satisfy a specific condition
DEFAULT Set a default value.
INDEX Optimise retrieving data.


Primary key
Uniquely identifies each record in a table. Cannot be null and has to be unique. A table can have only one primary key, which may consist of single or multiple fields.

CREATE TABLE myTable (ID int NOT NULL PRIMARY KEY,…);
CREATE TABLE myTable (ID int NOT NULL, …, CONSTRAINT myColumn PRIMARY KEY (ID,LastName));

Foreign key
CREATE TABLE Orders (
idColumn int NOT NULL, PRIMARY KEY (idColumn), FOREIGN KEY (columnForeignKey) REFERENCES otherTable(idColumnInOtherTable)
);

Check
CREATE TABLE myTable (ID int NOT NULL, …, intColumn int, CHECK (intColumn>=18));

Default
CREATE TABLE Persons (ID int NOT NULL, …, stringColumn varchar(255) DEFAULT ‘someString’);

Index
CREATE INDEX indexName ON myTable (column1, column2, …);
Create an index on a combination of columns:
CREATE UNIQUE INDEX indexName ON myTable (column1, column2, …);
DROP INDEX indexName ON myTable;

Auto increment
Auto-increment generates a unique number when a new row is inserted.
CREATE TABLE myTable (ID int NOT NULL AUTO_INCREMENT,…);
ALTER TABLE myTable AUTO_INCREMENT=100;

Oracle
CREATE SEQUENCE seq_person MINVALUE 1 START WITH 1 INCREMENT BY 1 CACHE 10;