Constraints are the rules enforced on table. These are used to restrict the type or value of data that can be inserted into it.
Constraints could be either on a column level or a table level. The column level constraints are applied only to one column, whereas the table level constraints are applied to the whole table.
They can applied when a table is created or when its structure is altered.
To verify the accuracy and reliability of the data in the database and also to prevent any accidental damage to it.
Column Level: Entity Integrity Constraint
NOT NULL, UNIQUE, DEFAULT, CHECK
- NOT NULL – doesn’t allow values of column to be null
- UNIQUE – doesn’t allow duplicate values in a column
- DEFAULT – specifies default value of a column that is taken when there’s no value passed for it.
- CHECK – checks if certain condition is true and doesn’t allow values to be entered that don’t satisfy the condition
Example to demonstrate how to apply different constraints:
CREATE TABLE STUDENT( /* This is a comment*/ --This is a comment too ROLL_NO INT NOT NULL UNIQUE,-- Note: More than 1 constraint can be applied on 1 field STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT CHECK (STU_AGE >4), EXAM_FEE INT DEFAULT 10000, STU_ADDRESS VARCHAR (<DEFAULT LEN>=50) , PRIMARY KEY (ROLL_NO) );
Primary Key Constraint
Primary key is to uniquely identify each record in a table. It must have unique values and cannot contain nulls. Thus PRIMARY KEY = NOT NULL+ UNIQUE.
Foreign Key Constraint
Foreign keys are the fields of a table that point to the primary key of another table. They act as a cross-reference between tables. It can applied like:
c_id int FOREIGN KEY REFERENCES Customer_Detail(c_id)
Used to create and retrieve data from the database very quickly and can be used as Primary key in case of tables with no fields satisfying the key criteria.
Example of Primary, foreign key is given below:
CREATE TABLE Student (
Student_id int PRIMARY KEY NOT NULL,
constraint fk_Course foreign key (course_id) /*foreign key constraint is aliased that is given a name <fk_Course>so as we can access it directly in case we want to drop it in future.*/
references dbo.Course /*dabasename.table_name_referred*/
CREATE TABLE Course (
course_id int PRIMARY KEY NOT NULL, /*This is referred in above table*/
Specifies valid data type for attributes or fields and usually include domain like string, character, integer, time, date, currency, etc. Specifying domain constraint is necessary for each field.
Referential Integrity Constraint
This is to uniquely identify a row/record in not only current table but in any of the given database table. Other constraints like Primary/Foreign Key , UNIQUE are to satisfy Referential Integrity.
For eg: If there are two tables Student Table and Course Table with fields (Student_id,Name,Course_ID) & (Course_ID, Subject Name,Instructor) then in student table’s Course_ID field, we could not have a course whose ID isn’t mentioned in Course Table; since it would mean a student has opted for a course not offered. To maintain integrity in such a situation Referential Integrity concept is used.