Various Constraints in DBMS | Database Management Systems

0
143


What are constraints?

Constraints are the rules enforced on table. These are used to restrict the type or value of data that can be inserted into it.

Where can we apply constraints?

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.

When can we apply constraints?

They can applied when a table is created or when its structure is altered.

Why are constraints used?

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)
);

Key Constraints

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)

Indexing Concept

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,
first_name varchar(20),
last_name varchar(20),

course_id int,
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*/
instructor_name varchar(30),
Course_name varchar(50),
);

Domain Constraints

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.