1
CSC371-DATABASE SYSTEMS I
LECTURE-5 (LAB)
2
PREVIOUS LECTURE REVIEW
• SQL Constraints
• NOT NULL
• Primary Key
• Unique
• Default
3
• CREATE TABLE Staff (
CREATE TABLE
staffNo int,
fName varchar(255),
lName varchar(255),
position varchar(255),
gender char ,
DOB date,
salary money,
);
4
AGENDA
• SQL Constraints
• CHECK
• INDEX
• FOREIGN KEY
5
SQL CONSTRAINTS
• CHECK - Ensures that all values in a column satisfies a
specific condition
• INDEX - Used to create and retrieve data from the
database very quickly
FOREIGN KEY - Uniquely identifies a row/record in
another table
6
SQL CHECK ON CREATE TABLE
• SQL Server CHECK Constraint • MySQL CHECK Constraint
• CREATE TABLE Staff ( • CREATE TABLE Staff (
staffNo int NOT NULL, staffNo int NOT NULL,
fName varchar(255), fName varchar(255),
lName varchar(255), lName varchar(255),
position varchar(255), position varchar(255),
DOB date, DOB date,
salary money salary money,
CHECK (salary>=17000) CHECK (salary>=17000)
); );
7
CHECK CONSTRAINT ON MULTIPLE COLUMNS
• CREATE TABLE Staff (
staffNo int NOT NULL UNIQUE,
fName varchar(255),
lName varchar(255),
position varchar(255),
gender char ,
DOB date,
salary money,
CONSTRAINT CHK_Staff CHECK (salary>=17000
AND position=‘Supervisor')
);
8
SQL CHECK ON ALTER TABLE
• For Single Column
• ALTER TABLE staff
ADD CHECK (salary>=17000);
• ALTER TABLE staffADD CHECK (position='supervisor' or position='Manager');
• For Multiple Column
• ALTER TABLE staff
ADD CONSTRAINT CHK_staff CHECK (salary>=17000
AND position=‘Supervisor');
9
DROP A CHECK CONSTRAINT
• SQL Server • MySQL
• ALTER TABLE Staff • ALTER TABLE Staff
DROP CONSTRAINT CHK_staff; DROP CHECK CHK_staff;
10
SQL CREATE INDEX STATEMENT
• CREATE INDEX index_name
ON table_name (column1, column2, ...);
• CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
11
SQL CREATE INDEX STATEMENT
• CREATE INDEX idx_lname
ON staff (lName);
• CREATE INDEX idx_sname
ON staff (lName, fName);
12
DROP INDEX STATEMENT
• SQL Server:
• DROP INDEX staff. idx_sname;
• MySQL:
• ALTER TABLE staff
DROP INDEX idx_sname;
13
FOREIGN KEY CONCEPT
14
SQL FOREIGN KEY CONSTRAINT
• MS SQL Server • CREATE TABLE Staff (
staffNo int NOT NULL PRIMARY KEY,
fName varchar(255),
• CREATE TABLE Branch ( lName varchar(255),
branchNo varchar(10) position varchar(255),
NOT NULL sex char ,
PRIMARY KEY, DOB date,
salary money,
street varchar(255),
branchNo varchar(10) ,
city varchar(255), CONSTRAINT FK_BranchStaff FOREIGN KEY (branchNo)
REFERENCES Branch(branchNo)
postCode varchar(255),
);
);
15
SQL FOREIGN KEY ON ALTER TABLE
• ALTER TABLE Staff
ADD FOREIGN KEY (branchNo) REFERENCES Branch(branch
No);
• Add Foreign key Constraint with Constraint Name
• ALTER TABLE Staff
ADD CONSTRAINT FK_BranchStaff
FOREIGN KEY (branchNo) REFERENCES Branch(branchNo);
16
DROP A FOREIGN KEY CONSTRAINT
• MS SQL SERVER • MySQL
• ALTER TABLE Staff • ALTER TABLE Staff
DROP CONSTRAINT DROP FOREIGN KEY
FK_BranchStaff; FK_BranchStaff;
17
BONUS
SQL AUTO INCREMENT FIELD 18
• CREATE TABLE Persons (
Personid int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
• INSERT INTO Persons (FirstName, LastName, age)
VALUES ('Lars','Monsen‘,30),(‘Malinda’,’John’,32);
• Select * from Persons
• Select Personid,FirstName,LastName,Age from Persons
19
SQL AUTO INCREMENT FIELD
FOR MYSQL
• CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
• ALTER TABLE Persons AUTO_INCREMENT=100;
20
SUMMARY
• SQL Constraints
• CHECK
• INDEX
• FOREIGN KEY
