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
NOT NULL Constraint
UNIQUE Constraint
CHECK Constraint
DEFAULT Constraint
FOREIGN KEY Constraint
Example 1: Using Primary Key, Not Null, Unique and Check Constraints
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));
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
Example
Removing the constraints
Example
ALTER TABLE book DROP PRIMARY KEY;
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
Post a Comment