BeginnersBook

  • Home
  • Java
    • Java OOPs
    • Java Collections
    • Java Examples
  • C
    • C Examples
  • C++
    • C++ Examples
  • DBMS
  • Computer Network
  • Python
    • Python Examples
  • More…
    • jQuery
    • Kotlin
    • WordPress
    • SEO
    • JSON
    • JSP
    • JSTL
    • Servlet
    • MongoDB
    • XML
    • Perl

Constraints in DBMS

By Chaitanya Singh | Filed Under: 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. In this tutorial 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
  • Domain constraints
  • Mapping constraints

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

Read more about this constraint here.

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

Read more about it here.

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

Read more: Default constraint

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

In the above example we have set the check constraint on ROLL_NO column of STUDENT table. Now, the ROLL_NO field must have the value greater than 1000.

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 KEY:

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.
Read more about it here.

Domain constraints:

Each table has certain set of columns and each column allows a same type of data, based on its data type. The column does not accept values of any other data type.
Domain constraints are user defined data type and we can define them like this:

Domain Constraint = data type + Constraints (NOT NULL / UNIQUE / PRIMARY KEY / FOREIGN KEY / CHECK / DEFAULT)

Mapping constraints:

Read about Mapping constraint here.

❮ PreviousNext ❯

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

DBMS Tutorial

Basics

  • DBMS Tutorial
  • DBMS Introduction
  • Database Applications
  • DBMS vs File System
  • DBMS vs RDBMS
  • DBMS Architecture
  • Three-level DBMS architecture
  • View in DBMS
  • Abstraction
  • Instance & Schema
  • DBMS languages

Data Models

  • Data Models
  • ER Diagram
  • ER Design issues
  • Convert ER to table
  • DBMS Generalization
  • DBMS Specialization
  • DBMS Aggregration
  • Relational Model
  • Hierarchical Model
  • Constraints
  • Cardinality

Relational Database

  • RDBMS concepts
  • Relational Algebra
  • Relational Calculus
  • Keys Index
  • Primary Key
  • Super Key
  • Candidate Key
  • Foreign Key
  • Composite Key
  • Alternate Key

Normalization

  • Normalization
  • Functional dependency

Transaction Management

  • Transaction Management
  • ACID properties
  • Transaction States
  • DBMS Schedules
  • Serializability
  • Conflict Serializability
  • View Serializability
  • Recoverability Of Schedule
  • Failure Classification
  • Log based Recovery
  • DBMS Checkpoint
  • Deadlock

Concurrency Control

  • Concurrency Control
  • Lock based protocol
  • Timestamp based protocol
  • Validation based protocol

File Organization

  • File Organization
  • Sequential File Organization
  • Heap File Organization
  • Hash File Organization
  • DBMS ISAM
  • B+ File Organization
  • Cluster File Organization

SQL Introduction

  • SQL Introduction
  • SQL Characteristics
  • Advantages of SQL
  • SQL Commands
  • SQL Operators
  • SQL CREATE
  • SQL DROP
  • SQL SELECT
  • SQL INSERT

Copyright © 2012 – 2022 BeginnersBook . Privacy Policy . Sitemap