Types of CONSTRAINTS in SQL - Study24x7
Social learning Network
study24x7

Default error msg

Login

New to Study24x7 ? Join Now
Already have an account? Login
googlePlay app_img

Types of CONSTRAINTS in SQL

Updated on 16 June 2020
study24x7
All About Databases
7 min read 12 views
Updated on 16 June 2020
Share this article
Copy Link Link Copied
NAT Banner

The SQL Constraints are an integrity which defines some conditions that restrict the column to stay true while inserting or updating or deleting data within the column. Constraints are often specified when the table created first with CREATE TABLE statement or at the time of modification of the structure of an existing table with ALTER TABLE statement.


The SQL Constraints are used to implement the rules of the table. If there's any violation of the constraints caused some action not performing properly on the table the action is aborted by the constraint. Some Constraints are often used along side the SQL CREATE TABLE statement.


The general structure of the SQL Constraint is defined as:


The CONSTRAINT keyword is followed by a constraint name followed by a column or an inventory of columns.


Constraints are wont to specify rules for the info during a table. Constraints are wont to limit the sort of knowledge which will enter a table. This ensures the accuracy and reliability of the info within the table. If there's any violation between the constraint and therefore the data action, the action is aborted.


Integrity constraint violations. Integrity constraint violations occur when an insert, update, or delete statement violates a primary key, foreign key, check, or unique constraint or a singular index.


NOT NULL constraint

The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain a worth , which suggests that you simply cannot insert a replacement record, or update a record without adding a worth to the present field.

CREATE TABLE Persons (

   ID int NOT NULL,

    LastName varchar(255) NOT NULL,

   FirstName varchar(255) NOT NULL,

   Age int

);


Unique constraint

A unique constraint may be a single field or combination of fields that uniquely defines a record. Some of the fields can contain null values as long because the combination of values is exclusive .

CREATE TABLE Persons (

   ID int NOT NULL,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Age int,

    UNIQUE (ID)

);



PRIMARY KEY constraint

The PRIMARY KEY constraint uniquely identifies each record during a table. Primary keys must contain UNIQUE values, and can't contain NULL values. A table can have just one primary key; and within the table, this primary key can contains single or multiple columns (fields)

CREATE TABLE Persons (

   ID int NOT NULL,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Age int,

    CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)

);


Difference Between Unique constraint And PRIMARY KEY constraint

. Parameter                           Primary key                                 Unique key

Basic
Used to serve as a unique identifier for each row in a table.
Uniquely determines a row which isn’t primary key.
NULL value acceptance
Cannot accept NULL values.
Can accept one NULL value.
Number of keys that can be defined in the table
Only one primary key
More than one unique key
Index
Creates clustered index
Creates non-clustered index




Check constraint

A check constraint may be a sort of integrity constraint in SQL which specifies a requirement that has got to be met by each row during a database table. The constraint must be a predicate. It can ask one column, or multiple columns of the table.

CREATE TABLE Persons (

   ID int NOT NULL,

   LastName varchar(255) NOT NULL,

   FirstName varchar(255),

   Age int,

   City varchar(255),

    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')

);


FOREIGN KEY constraint

A FOREIGN key's a key wont to link two tables together.A FOREIGN key's a field (or collection of fields) in one table that refers to the first KEY in another table.The table containing the foreign key's called the kid table, and therefore the table containing the candidate key's called the referenced or parent table.

CREATE TABLE Orders (

   OrderID int NOT NULL,

   OrderNumber int NOT NULL,

   PersonID int,

    PRIMARY KEY (OrderID),

    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)

    REFERENCES Persons(PersonID)

);


informational constraint

An informational constraint may be a constraint attribute which will be employed by the SQL compiler to enhance the access to data. Informational constraints aren't enforced by the database manager, and aren't used for extra verification of data; rather, they're wont to improve query performance.

Databases Microsoft Technologies Computer Programming
Like| 3 Comment
study24x7
Write a comment...
Trending Articles

Best Study timetable for class 8th students

By CBSE CLASS 8

HOW TO WRITE CERTIFICATE SECTION FOR CLASS XII SCHOOL PROJE...

By Edubabble LLP

Best topic ideas for class 7 Science projects

By CBSE CLASS 7

The best daily routine for class 8 students

By CBSE CLASS 8
Featured Courses
Related Posts

Check these related articles on Study24x7

What is Social Learning Network and How it is changing the way of Learning
31 min read 106 views

A lot of changes have taken place in the past few months due to the sudden outbreak of the coronavirus pandemic. In such times, one major change that

Study24x7

1218 followers

Why Is Artificial Intelligence The Future Of Growth?
35 min read 57 views

The concept of Artificial Intelligence has been around for centuries and here is why artificial intelligence is the future of growth. At its core, AI

Study24x7

1218 followers

2 Eggs and 100 Floors Puzzle
7 min read 34 views

Puzzle Definition You are given two eggs, and access to a 100-storey building.Both eggs are identical. The aim is to find out the highest floor from

Data Structure

72 followers

Encourage Healthy Eating Habits: From Picky Eaters to Food Explorers
30 min read 4 views

Picky Eaters: Picky eaters are children who have strong preferences or aversions towards certain foods. They may exhibit selective eating habits,

TOPLINE

31 followers

Types of Database and its Uses
7 min read 13 views

There are four types of database: Text database Desktop database Relational database No SQL and object oriented database Text database:  A text d

All About Databases

23 followers

Test Artifacts
7 min read 4 views

Maintaining transparency among team members and stakeholders is extremely important nowadays. From updating them about the process to delivering vario

Shubham Kumar

88 followers

Benefits of Programming languages
6 min read 24 views

Computer is a machine and we can use it for multiple purposes, At one time, you can listen to the music and do your work on computer applications. Fir

COMPUTER PROGRAMMING 2020

38 followers

How Artificial Intelligence (AI) Is Changing Our Society And Its Impact On the future?
26 min read 74 views

As with most changes in life, there will be both positive and negative effects on society as artificial intelligence continues to change the world we

Study24x7

1218 followers

Share this article
Copy Link Link Copied