SQL Constraints

    Constrains are the special rules which restrict the limits of the column data values, constrains often used when we want a column data values to follow a specific set of rule. It's always a good practice to mention the column constrain if necessary because it increases the data reliability and accuracy.

    SQL Constraints

    As we know that constraints are specified to the columns, which mean we can specify a constrain to a column when we create the table or when we alter the table columns. Syntax 

    CREATE TABLE table_name (
    column_name_1 datatype constraint,
    column_name_2 datatype constraint,
    column_name_3 datatype constraint,
    ..
);

    We can also apply constraints on the table.

    Types of Constraints

    Here is the list of most common constraints used in SQL:

    • NOT NULL
    • UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK
    • DEFAULT
    • INDEX

    1. NOT NULL

    If we do not mention any specific constraint with the column then that column data could have a NULL value, and the NOT NULL constraints specify that the column could not have any NULL value. Example(NOT NULL with Table creation) 

    CREATE TABLE Employee(
    ID int NOT NULL,
    FirstName varchar(255) NOT NULL,
    Age int
);

    Example(NOT NULL with Column Creation) 

    ALTER TABLE Employee
MODIFY Age int NOT NULL;

    2. Unique

    Unique make sure that no two data sets have the same values in the respective column, if we mention a column with unique constraints then all values of that column should have distinct values. Example 

    CREATE TABLE Employee (
Name VARCHAR(20) NOT NULL,
IdCode VARCHAR(40) UNIQUE 
);

    3. Primary Key

    Primary Key is the special constraints in a table which also act as a bridge between two joined tables. If a table column is specified as a Primary Key then all the values of that column must have unique values and there should be no NULL value in that column. Unlike other constraints, a Table could only have a single Primary Key. Example 

    CREATE TABLE Employee(
    ID int PRIMARY KEY,
    Name varchar(255) NOT NULL,
);

    4. Foreign Key

    The Foreign key is used to link or join two tables, if we want to join two tables then both the table should share a similar field which can link them together, that field is known as a Foreign field and using the Foreign key we can create a foreign field. If a table column is marked as a Foreign Key then it would follow all the properties of the primary key, if we do not mention any foreign key in a table then by default the primary key act as the foreign key. Example 

    CREATE TABLE Employee(
    ID int PRIMARY KEY,
    Name varchar(255) NOT NULL,
);
    CREATE TABLE Project(
    ProjectName VARCHAR(10),
    ID int FOREIGN KEY REFERENCES Employee(ID)
);

    5. Check

    The check constraint makes sure that each data of that column must satisfy a specific condition. Example: 

    CREATE TABLE Employee(
   ID int PRIMARY KEY,
   Name varchar(255) NOT NULL,
   Age int,
   CHECK (Age>=18)
);

    6. Default

    With Default constraints, we can set a default value for the column if the user does not want to fill that data field. 

    CREATE TABLE Employee(
   ID int PRIMARY KEY,
   Name varchar(255) NOT NULL,
   Address vachar(1234) DEFAULT 'Delhi' 
);

    7. Index

    With Index constraints we can specify or create indexes in the table, the concept of Index in the table is similar to the indexes in the array, the only purpose of the Index to speed up the search queries. Syntax 

    CREATE INDEX index_name
ONtable_name (column1, column2, ...);

    8. Deleting Column Constraints

    With the help of SQL DROP command, we can delete the column constraints, by dropping the column constant the data and the table columns values do not get affected. Example 

    ALTER TABLE EMPLOYEES DROP CONSTRAINT Primary Key;

    Summary

    • Constraints are used restricts the limits of the table columns data values.
    • There are 7 common constraints used in SQL.
    • Constraints can be mentioned while creating a table or altering the table columns.
    • Using the drop command we can delete the column constraints.

    People are also reading:

    Previous Tutorial Next Tutorial