SQL CONSTRAINTS

1. NOT NULL

• NOT NULL means the column is a mandatory field.
• If a user tries to insert or update a record without providing a value for this column, it will cause an error.

column_name data_type NOT NULL

Example:

Name VARCHAR(50) NOT NULL

2. UNIQUE

• UNIQUE columns cannot have duplicate values, but depending on the database, multiple NULLs may be allowed.
• Attempting to insert duplicate values into a UNIQUE column will result in an error.

column_name data_type UNIQUE

Example:

Email VARCHAR(100) UNIQUE

3. PRIMARY KEY

• PRIMARY KEY columns automatically have NOT NULL and UNIQUE properties. You don’t need to specify them separately.
• A table can only have one PRIMARY KEY.
• PRIMARY KEY can be defined on multiple columns (composite key).

Column-level:

column_name data_type PRIMARY KEY

Table-level (can be used with multiple columns):

CREATE TABLE table_name (
    column1 data_type,
    column2 data_type,
    ...
    PRIMARY KEY (column1, column2)
);

Example:

StudentID INT PRIMARY KEY

4. FOREIGN KEY

• FOREIGN KEY creates a relationship between two tables.
• Only values that exist in the referenced (parent) table can be inserted into the foreign key column.
• It enforces referential integrity; for example, an order must belong to an existing customer

Table-level syntax:

FOREIGN KEY (column_name) REFERENCES other_table(other_column)

Example:

FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)

Get seyma akın’s stories in your inbox

Join Medium for free to get updates from this writer.

Remember me for faster sign in

5. CHECK

• CHECK constraint sets rules for values in a column.
• For example, age cannot be less than zero.
• Multiple conditions can be combined using AND and OR.

column_name data_type CHECK (column_name condition)

Example:

Age INT CHECK (Age >= 18)

Alternative table-level syntax:

CHECK (Age >= 18)

6. DEFAULT

• DEFAULT provides an automatic default value if none is supplied during insert.
• Often used for fixed texts (e.g., 'USA') or system-generated values like current date (GETDATE()).
• If the user does not enter a value, this default is saved.

column_name data_type DEFAULT default_value

Example:

Stock INT DEFAULT 0

7. CREATE INDEX

• Used to speed up searches and sorting on a table.
• It does not change the physical structure of data.
• Not necessary for every SELECT query, only useful on frequently searched columns.
• UNIQUE INDEX can also enforce uniqueness.

CREATE INDEX index_name ON table_name(column_name);

Example:

CREATE INDEX idx_Name ON Students(Name);

ADDING CONSTRAINTS AFTER TABLE CREATION (Using ALTER TABLE)

For NOT NULL / UNIQUE / DEFAULT type constraints:

ALTER TABLE table_name
ALTER COLUMN column_name data_type CONSTRAINT_TYPE;

For FOREIGN KEY, CHECK, PRIMARY KEY, etc.:

ALTER TABLE table_name
ADD CONSTRAINT constraint_name CONSTRAINT_TYPE;

Example:

ALTER TABLE Employees
ADD CONSTRAINT chk_Age CHECK (Age >= 18);