Constraints
The following constraints are commonly used in SQL:
NOT NULL - Ensures that a column cannot have a NULL value
UNIQUE - Ensures that all values in a column are different
PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each
row in a table
FOREIGN KEY - Prevents actions that would destroy links between tables
CHECK - Ensures that the values in a column satisfies a specific condition
DEFAULT - Sets a default value for a column if no value is specified
NOT NULL
CREATE TABLE Customer ( ID int NOT NULL, LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL, Age int );
desc Customer;
ALTER COMMAND
ALTER TABLE Customer
MODIFY Age int NOT NULL;
desc Customer;
UNIQUE CONSTRAINT
CREATE TABLE student (
ID int ,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
(or)
CREATE TABLE student (
ID int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
ALTER TABLE student
ADD UNIQUE (ID);
ALTER TABLE student
ADD CONSTRAINT uc_student UNIQUE (ID,LastName);
ALTER TABLE student
DROP INDEX uc_student;
PRIMARY KEY CONSTRAINT
CREATE TABLE student (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
(or)
CREATE TABLE student (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
ALTER TABLE student
ADD PRIMARY KEY (ID);
Composite primary key:
CREATE TABLE user (
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT pk_user PRIMARY KEY (FirstName,LastName)
);
ALTER TABLE student
ADD CONSTRAINT pk_user PRIMARY KEY (FirstName,LastName);
ALTER TABLE student
DROP PRIMARY KEY ;
FOREIGN CONSTRAINT
CREATE TABLE Persons (
PID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (PID)
);
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
FOREIGN KEY (PersonID) REFERENCES Persons(PID)
);
CREATE TABLE Orders (
OrderID int NOT NULL,
OrderNumber int NOT NULL,
PersonID int,
PRIMARY KEY (OrderID),
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
REFERENCES Persons(PID)
);
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID) REFERENCES Persons(PID);
ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PID);
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
CHECK CONSTRAINT
CREATE TABLE student (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
CREATE TABLE student (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_student CHECK (Age>=18 AND City='Coimbatore')
);
ALTER TABLE student
ADD CHECK (Age>=18);
ALTER TABLE student
ADD CONSTRAINT CHK_studentAge CHECK (Age>=18 AND City='Coimbatore');
ALTER TABLE student
DROP CHECK CHK_studentAge;
DEFAULT CONSTRAINT
CREATE TABLE student (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'coimbatore'
);
ALTER TABLE student
ALTER City SET DEFAULT 'coimbatore';
ALTER TABLE student
ALTER City DROP DEFAULT;
Evaluation1 - Aug 19th to 31st
Quiz1 - Aug 19th to 31st
Evaluation 2 – september end week
Evaluation 3 – October last week
Quiz2 - October last week
