2 min read

Constraints in DBMS

Constraints enforce limits to the data or type of data that can be Inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.
Constraints in DBMS

In this blog we will learn several types of constraints that can be created in RDBMS.

Types of constraints

  • NOT NULL
  • UNIQUE
  • DEFAULT
  • CHECK
  • Key Constraints – PRIMARY KEY, FOREIGN KEY

NOT NULL:

NOT NULL constraint makes sure that a column does not hold NULL value. When we don’t provide value for a particular column while inserting a record into a table, it takes NULL value by default. By specifying NULL constraint, we can be sure that a particular column(s) cannot have NULL values.

Example:

CREATE TABLE STUDENT( ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (235), PRIMARY KEY (ROLL_NO) );

UNIQUE:

UNIQUE Constraint enforces a column or set of columns to have unique values. If a column has a unique constraint, it means that particular column cannot have duplicate values in a table.

CREATE TABLE STUDENT(ROLL_NO INT NOT NULL,STU_NAME VARCHAR (35) NOT NULL UNIQUE,STU_AGE INT NOT NULL,STU_ADDRESS VARCHAR (35) UNIQUE,PRIMARY KEY (ROLL_NO));

DEFAULT:

The DEFAULT constraint provides a default value to a column when there is no value provided while inserting a record into a table.

CREATE TABLE STUDENT( ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, EXAM_FEE INT DEFAULT 10000, STU_ADDRESS VARCHAR (35) , PRIMARY KEY (ROLL_NO) );

CHECK:

This constraint is used for specifying range of values for a particular column of a table. When this constraint is being set on a column, it ensures that the specified column must have the value falling in the specified range.

CREATE TABLE STUDENT( ROLL_NO INT NOT NULL CHECK(ROLL_NO >1000) , STU_NAME VARCHAR (35) NOT NULL, STU_AGE INT NOT NULL, EXAM_FEE INT DEFAULT 10000, STU_ADDRESS VARCHAR (35) , PRIMARY KEY (ROLL_NO) );

Key constraints:

PRIMARY KEY:

Primary key uniquely identifies each record in a table. It must have unique values and cannot contain nulls. In the below example the ROLL_NO field is marked as primary key, that means the ROLL_NO field cannot have duplicate and null values.

CREATE TABLE STUDENT( ROLL_NO INT NOT NULL, STU_NAME VARCHAR (35) NOT NULL UNIQUE, STU_AGE INT NOT NULL, STU_ADDRESS VARCHAR (35) UNIQUE, PRIMARY KEY (ROLL_NO) );

Foreign keys are the columns of a table that points to the primary key of another table. They act as a cross-reference between tables.

Constraints in DBMS
Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain
https://beginnersbook.com/2015/04/constraints-in-dbms/

#DBMS #Constraints #Probyto #ProbytoAI

Subscribe and follow us for latest news in Data Science, Machine learning, technology and stay updated!

Facebook: https://facebook.com/probyto
Twitter: https://twitter.com/probyto
LinkedIn: https://linkedin.com/company/probyto
Instagram: https://instagram.com/probyto