MSSQL Server Constraints
There are six constraints which are NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, DEFAULT. I will explain all of them with giving example for each one.
NOT NULL : If you use this constraint for a column then this column cannot contain null value.
UNIQUE : Ensures that every column has a unique value.
PRIMARY KEY : Primary key has two specific rules If a column has primary key constraint then it must be not null and unique. When we use primary key it will help us to find record which we are searching correctly.
FOREIGN KEY : This constraint ensures the value in a column in a table to match value in a column in another table.
CHECK :Ensures which the value in a column meets a specific condition.
DEFAULT : It is using for put a default value when create new record for a column.
I created three entity in visual paradigm to give examples
Using Constraints while creating table
NOT NULL
CREATE TABLE STUDENTS( StudentName varchar(25) NOT NULL, StudentLastName varchar(25) NOT NULL );
UNIQUE
CREATE TABLE STUDENTS( StudentID int NOT NULL UNIQUE, StudentName varchar(25) NOT NULL, StudentLastName varchar(25) NOT NULL );
PRIMARY KEY
CREATE TABLE STUDENTS( StudentID int IDENTITY(1,1) NOT NULL, StudentName varchar(25) NOT NULL, StudentLastName varchar(25) NOT NULL );
FOREIGN KEY
CREATE TABLE SCHEDULE( ScheduleID int IDENTITY(1,1) NOT NULL, StudentID int FOREIGN KEY REFERENCES STUDENTS(StudentID), LectureID int FOREIGN KEY REFERENCES LECTURES(LectureID), );
CHECK
CREATE TABLE STUDENTS( StudentID int IDENTITY(1,1) NOT NULL, StudentName varchar(25) NOT NULL, StudentLastName varchar(25) NOT NULL, StudentAge int CHECK (StudentAge>0) );
DEFAULT
CREATE TABLE STUDENTS( StudentID int IDENTITY(1,1) NOT NULL, StudentName varchar(25) NOT NULL, StudentLastName varchar(25) NOT NULL, StudentregistrationDate DEFAULT GETDATE() );
Using constraints for already created table
UNIQUE
ALTER TABLE STUDENTS ADD UNIQUE (StudentID)
PRIMARY KEY
ALTER TABLE STUDENTS ADD PRIMARY KEY (StudentID)
FOREIGN KEY
ALTER TABLE SCHEDULE ADD FOREIGN KEY (StudentID) REFERENCES STUDENTS(StudentID)
CHECK
ALTER TABLE STUDENTS CHECK (StudentAge>0)
DEFAUL
ALTER TABLE STUDENTS ALTER COLUMN StudentAge SET DEFAULT 0