SQL Constraints- Free SQL tutorials

SQL Constraints:

SQL Constraints are the set of rules followed by SQL programming language. SQL is a procedural programming language.They are used to specify rules for the data. They put a condition , and limit to data entering the table.

SQL Constraints could be divided into ‘2’ levels: Constraints could be of ‘Table level’ or they could also be applied on individual ‘column’. Table level Constraints are applied over whole table( All values are affected by it.)

The following SQL constraints commonly are :

• NOT NULL – It Ensures that a column cannot have a NULL value
• UNIQUE – It Ensures that all values in a column are different to each other
• PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Primary Key are unique for any table.
• FOREIGN KEY – Uniquely identifies a row/record in another table.Primary key for table ‘A’ would be considered as ‘FOREIGN’ key for other tables.
• CHECK – It Ensures that all values in a column satisfies a specific condition
• DEFAULT -It  Sets a default value for a column when no value is specified
• INDEX -It is  Used to create and retrieve data from the database.

---

Pratical approach to constraints:

NOT NULL:

Example:NOT NULL constraint forces a column to neglect null values. ID,CGPA,Company attributes ought to have values and can not have NULL value.

CREATE TABLE Persons (
    ID int NOT NULL,
    CGPA int NOT NULL,
    Company varchar(255) NOT NULL,
    Age int
);

UNIQUE:

Example:Combination of  NOT NULL+UNIQUE=>Primary Key

CREATE TABLE Persons (
    ID int NOT NULL,
    CGPA int NOT NULL,
    company varchar(255),
    Age int,
    UNIQUE (ID)
);#MYsql

Primary Key:

Example:Primary Key ought to have unique values, and can not have NULL values. NOT NULL+UNIQUE=>Primary Key

CREATE TABLE Persons (
    ID int NOT NULL,
    cgpa int  NOT NULL,
    company varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Foreign Key:

In the above example of Primary key , ID was the primary key in persons table. Primary key for a particular table is a foreign key for any secondary table.Let us try to clear our concept with example:

CREATE TABLE Persons (
    ID int NOT NULL,
    cgpa int  NOT NULL,
    company varchar(255),
    Age int,
    PRIMARY KEY (ID)
);

Primary key: ID

Table 2:

CREATE TABLE Persons (
    ID int NOT NULL,
    cgpa int  NOT NULL,
    company varchar(255),
    Age int,
   FOREIGN KEY (ID) REFERENCES Persons(ID)
);

CHECK:

SQL CHECK constraints had already been discussed .‘CHECK’ constraint provides us with a condition , if condition fails then values are discarded and not added accordingly.

DEFAULT:

The DEFAULT constraint are used to set a default value for column.If no other value is specified then default values are specified to the following field.

CREATE TABLE Persons (
    ID int NOT NULL,
    cgpa int NOT NULL,
    company varchar(255),
    Age int,
    company varchar(255) DEFAULT 'xyzxyz'
);

company default value-xyzxyz

INDEX:

Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.

Syntax:

CREATE INDEX index_name
ON table_name (column1, column2, ...);

example:

CREATE INDEX idx_cgpa
ON Persons (cgpa);

Indexes couldn’t be seen . Indexes saves our time and helps user to retreive data in less time.Index for cgpa is created.