sql constraints

1NOT NULL 	 # Ensures a column cannot have a NULL value
2UNIQUE 		 # Ensures all values in a column are unique
3PRIMARY KEY  # Identifies a record in a table, is NOT NULL & UNIQUE
4FOREIGN KEY  # References a unique record from another table
5CHECK		 # Ensures all column values satisfy a condition
6DEFAULT		 # Set a default value for a column if none is entered
7INDEX		 # Quick way of retrieving records from database

1NOT NULL - Ensures that a column cannot have a NULL value
2UNIQUE - Ensures that all values in a column are different
3PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
4
5CREATE TABLE table_name (
6    column1 datatype constraint,
7    column2 datatype constraint,
8    column3 datatype constraint,
9    ....
10);
11
12CREATE TABLE Persons (
13    ID int NOT NULL,
14    LastName varchar(255) NOT NULL,
15    FirstName varchar(255) NOT NULL,
16    Age int
17);
18
19CREATE TABLE Persons (
20    ID int NOT NULL,
21    LastName varchar(255) NOT NULL,
22    FirstName varchar(255),
23    Age int,
24    UNIQUE (ID)
25);
26
27CREATE TABLE Persons (
28    ID int NOT NULL,
29    LastName varchar(255) NOT NULL,
30    FirstName varchar(255),
31    Age int,
32    PRIMARY KEY (ID)
33);
34

1RULES ABOUT THE COLUMNS IN TABLE
2NOT NULL 	 # Ensures a column cannot have a NULL value
3UNIQUE 		 # Ensures all values in a column are unique
4PRIMARY KEY  # Identifies a record in a table, is NOT NULL & UNIQUE
5FOREIGN KEY  # References a unique record from another table
6CHECK		 # Ensures all column values satisfy a condition
7DEFAULT		 # Set a default value for a column if none is entered
8INDEX		 # Quick way of retrieving records from database

1It creates a new constraint on an existing table, which is used to specify
2rules for any data in the table.
3Example: Adds a new PRIMARY KEY constraint named ‘user’ on columns
4ID and SURNAME.
5ALTER TABLE users
6ADD CONSTRAINT user PRIMARY KEY (ID, SURNAME);