Scribd
Upload
22 views8 pages

Key Constraints in DBMS

The document outlines key constraints in a Database Management System (DBMS), which are rules that govern data entry in database tables. It details five types of key constraints: NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY, and FOREIGN KEY, each serving specific purposes for data integrity and validation. Examples are provided for each constraint to illustrate their application in SQL table creation.

Uploaded by

M. Madhusudhan M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
22 views8 pages

Key Constraints in DBMS

The document outlines key constraints in a Database Management System (DBMS), which are rules that govern data entry in database tables. It details five types of key constraints: NOT NULL, UNIQUE, DEFAULT, CHECK, PRIMARY KEY, and FOREIGN KEY, each serving specific purposes for data integrity and validation. Examples are provided for each constraint to illustrate their application in SQL table creation.

Uploaded by

M. Madhusudhan M
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 8

Key Constraints in DBMS

Key Constraints in DBMS

 Constraints or nothing but the rules that are to be followed while


entering data into columns of the database table
 Constraints ensure that data entered by the user into columns
must be within the criteria specified by the condition
 For example, if you want to maintain only unique IDs in the
employee table or if you want to enter only age under 18 in the
student table etc
 We have 5 types of key constraints
o NOT NULL: ensures that the specified column doesn’t
contain a NULL value.
o UNIQUE : provides a unique/distinct values to specified
columns.
o DEFAULT: provides a default value to a column if none
is specified.
o CHECK :checks for the predefined conditions before
inserting the data inside the table.
o PRIMARY KEY: it uniquely identifies a row in a table.
o FOREIGN KEY: ensures referential integrity of the
relationship
Not Null
 Null represents a record where data may be missing data or data
for that record may be optional
 Once not null is applied to a particular column, you cannot
enter null values to that column and restricted to maintain
only some proper value other than null
 A not-null constraint cannot be applied at table level

Example

CREATE TABLE STUDENT


(
ID INT NOT NULL,
NAME VARCHAR (20) NOT NULL,
AGE INT NOT NULL,
ADDRESS CHAR (25) ,
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID)
);

 In the above example, we have applied not null on three


columns ID, name and age which means whenever a record is
entered using insert statement all three columns should
contain a value other than null
 We have two other columns address and salary, where not null
is not applied which means that you can leave the row as empty
or use null value while inserting the record into the table
Unique
 Sometimes we need to maintain only unique data in the column
of a database table, this is possible by using a unique constraint
 Unique constraint ensures that all values in a column are unique

Example

CREATE TABLE Persons (


ID int UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
);
In the above example, as we have used unique constraint on ID
column we are not supposed to enter the data that is already
present, simply no two ID values are same
DEFAULT
 Default clause in SQL is used to add default data to the columns
 When a column is specified as default with some value then all
the rows will use the same value i.e each and every time while
entering the data we need not enter that value
 But default column value can be customized i.e it can be
overridden when inserting a data for that row based on the
requirement

Example for DEFAULT clause

The following SQL sets a DEFAULT value for the “city” column when the
“emp” table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE emp (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'hyderabad'
);

 As a result, whenever you insert a new row each time


you need not enter a value for this default column that
is entering a column value for a default column is
optional and if you don’t enter the same value is
considered that is used in the default clause

Check
 Suppose in real-time if you want to give access to an application
only if the age entered by the user is greater than 18 this is done
at the back-end by using a check constraint
 Check constraint ensures that the data entered by the user for
that column is within the range of values or possible values
specified

Example for check constraint


CREATE TABLE STUDENT (
ID int ,
Name varchar(255) ,
Age int,
CHECK (Age>=18)
);
 As we have used a check constraint as (Age>=18) which
means values entered by the user for this age column while
inserting the data must be less than or equal to 18 otherwise
an error is shown
 Simply, the only possible values that the age column will
accept is [0 -17]

Primary Key
 A primary key is a constraint in a table which uniquely
identifies each row record in a database table by enabling one or
more the column in the table as primary key

Creating a primary key

A particular column is made as a primary key column by using the


primary key keyword followed with the column name
CREATE TABLE EMP (
ID INT
NAME VARCHAR (20)
AGE INT
COURSE VARCHAR(10)
PRIMARY KEY (ID)
);

 Here we have used the primary key on ID column then ID


column must contain unique values i.e one ID cannot be used
for another student.
 If you try to enter duplicate value while inserting in the row
you are displayed with an error
 Hence primary key will restrict you to maintain unique
values and not null values in that particular column
Foreign Key
 The foreign key constraint is a column or list of columns which
points to the primary key column of another table
 The main purpose of the foreign key is only those values are
allowed in the present table that will match to the primary key
column of another table

Reference Table

Example to create a foreign key


CREATE TABLE CUSTOMERS1(
ID INT ,
NAME VARCHAR (20) ,
COURSE VARCHAR(10) ,
PRIMARY KEY (ID)
);

Child Table
CREATE TABLE CUSTOMERS2(
ID INT ,
MARKS INT,
REFERENCES CUSTOMERS1(ID)
);

You might also like