SQL CONSTRAINTS


SQl CONSTRAINTS are rules used to limit the type of data that can go into a table, to maintain the accuracy and integrity of the data inside table.

  • NOT NULL Constraint: Ensures that a column cannot have NULL value.
  • DEFAULT Constraint: Provides a default value for a column when none is specified.
  • UNIQUE Constraint: Ensures that all values in a column are different.
  • PRIMARY Key: Uniquely identified each rows/records in a database table.
  • FOREIGN Key: Uniquely identified a rows/records in any another database table.
  • CHECK Constraint: The CHECK constraint ensures that all values in a column satisfy certain conditions.

NOT NULL Constraint

A NULL is not the same as no data.

NOT NULL constraint restricts a column from having a NULL value. Once NOT NULL constraint is applied to a column, you cannot pass a null value to that column. It enforces a column to contain a proper value. One important point to note about NOT NULL constraint is that it cannot be defined at table level.

Example

CREATE TABLE Student(stud_id INT NOT NULL, stud_ame VARCHAR(60))

DEFAULT Constraint

The DEFAULT constraint provides a default value to a column when the INSERT INTO statement does not provide a specific value.

Example

CREATE TABLE CUSTOMERS(
       id  INT              NOT NULL,
       name VARCHAR (20)     NOT NULL,
       salary   DECIMAL (18, 2) DEFAULT 5000.00,       
       PRIMARY KEY (id)
)

UNIQUE Constraint

UNIQUE constraint ensures that a field or column will only have unique values.

A UNIQUE constraint field will not have duplicate data.

UNIQUE constraint can be applied at column level or table level.

Example

CREATE TABLE student(stud_id INT NOT NULL UNIQUE, stud_name VARCHAR(60))

Primary Key Constraint

A primary key is a field in a table which uniquely identifies each row/record in a database table.

Primary keys must contain unique values.

A primary key column cannot have NULL values.

Exxample

CREATE TABLE student (stud_id INT PRIMARY KEY, stud_name VARCHAR(60) NOT NULL,)

FOREIGN Key Constraint

A foreign key is a key used to link two tables together. This is sometimes called a referencing key.

Foreign Key is a column or a combination of columns whose values match a Primary Key in a different table.

The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.

Example

customer_detail Table :
c_idCustomer_Nameaddress
111AshwaniDelhi
112SachinHaryana
113SounRohtak
order_detail Table :
Order_idOrder_Namec_id
10Order1111
11Order2113
12Order3112

In customer_detail table, c_id is the primary key which is set as foreign key in order_detail table. The value that is entered in c_id which is set as foreign key in order_detail table must be present in customer_detail table where it is set as primary key. This prevents invalid data to be inserted into c_id column of order_detail table.

CREATE TABLE order_detail(order_id INT PRIMARY KEY,
order_name VARCHAR(60) NOT NULL,
c_id INT FOREIGN KEY REFERENCES customer_detail(c_id))

CHECK Constraint

The CHECK Constraint enables a condition to check the value being entered into a record. If the condition evaluates to false, the record violates the constraint and isn't entered into the table.

Example

CREATE TABLE stud(sid int NOT NULL CHECK(s_id > 0), sname VARCHAR(60) NOT NULL)

Dropping Constraints:

Any constraint that you have defined can be dropped using the ALTER TABLE command with the DROP CONSTRAINT option.

For example, to drop the primary key constraint in the EMPLOYEES table, you can use the following command:

ALTER TABLE customer DROP CONSTRAINT cust_pk

Share this article on