What are the constraints in SQL? How to apply them?

While creating a table we can specify some limitations or rules or constraints on the columns of the table which get applied while inserting the records, updating the records and deleting the records.

1. PRIMARY KEY
2. NOT NULL
3. UNIQUE
4. CHECK
5. DEFAULT
6. FOREIGN KEY...REFERENCES...
PRIMARY KEY Constraint
When we want a field value to be unique and not to be blank then use PRIMARY KEY constraint

NOT NULL Constraint
When we want a filed not to be blank

UNIQUE Constraint
When we want a field to have unique data only. 

CHECK Constraint
When we want to apply some condition on the field before inserting or updating a value in the field.

DEFAULT Constraint
When we want to provide some default value to the field if no value provided.

FOREIGN KEY Constraint
When we are inserting some data that must be in some other table as primary data

Example 1: Using Primary Key, Not Null, Unique and Check Constraints
Create a table book having fields as bookno to be primary key, name not be null, email not to be blank and to be unique, status having default value as A for available, place the CHECK constraint on price not be less than 100. Assuming MySQL as RDBMS Software.

CREATE TABLE book(bookno int PRIMARY KEY, name VARCHAR(50) NOT NULL, email varchar(50) NOT NULL UNIQUE, status CHAR(1) DEFAULT 'A', price float CHECK (price>=100));
Example 2: Using Foreign Key Constraint

CREATE TABLE Customers (
    CustomerID int PRIMARY KEY,
    CustomerName VARCHAR(100) NOT NULL,
    MobileNumber CHAR(10) NOT NULL UNIQUE
);

CREATE TABLE Items (
    ItemCode int PRIMARY KEY,
    ItemName VARCHAR(50) NOT NULL UNIQUE
);

CREATE TABLE Orders (
    OrderID int PRIMARY KEY,
    ItemCode int,
    Quantity int NOT NULL,
    OrderDate TIMESTAMP,
    CustomerID int,
    FOREIGN KEY (ItemCode) REFERENCES Items(ItemCode),
    FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Adding the constraints after creation of table
Syntax
ALTER TABLE <tablename> ADD <constraintname> (fieldname);

Example
ALTER TABLE book ADD PRIMARY KEY (bookno);

Removing  the constraints
Syntax
ALTER TABLE <tablename> DROP <constraintname>;
ALTER TABLE <tablename> DROP <constraintname>(fieldname);

Example
ALTER TABLE book DROP PRIMARY KEY;
ALTER TABLE book DROP UNIQUE(email);

Comments

Popular posts from this blog

What is template?

Can we access private members of an instance?

What are wrapper classes in Java?