Best Online Tutorials

SQL Tutorial

Free SQL Tutorial

SQL Interview Questions

SQL Practice Questions

SQL Tips & Tricks

SQL Errors

SQL Books

Angular Tutorial

Free Angular Tutorial

Angular Interview Questions

Angular Practice Questions

Angular Tips & Tricks

Angular Errors

Angular Books

Node.js Tutorial

Free Node.js Tutorial

Node.js Interview Questions

Node.js Practice Questions

Node.js Tips & Tricks

Node.js Errors

Node.js Books

React.js Tutorial

Free React.js Tutorial

React.js Interview Questions

React.js Practice Questions

React.js Tips & Tricks

React.js Errors

React.js Books

SSIS Tutorial

Free SSIS Tutorial

SSIS Interview Questions

SSIS Practice Questions

SSIS Tips & Tricks

SSIS Errors

SSIS Books

SEO Tutorial

Free SEO Tutorial

SEO Interview Questions

SEO Practice Questions

SEO Tips & Tricks

SEO Errors

SEO Books

Javascript Tutorial

Free Javascript Tutorial

Javascript Interview Questions

Javascript Practice Questions

Javascript Tips & Tricks

Javascript Errors

Javascript Books

MongoDB Tutorial

Free MongoDB Tutorial

MongoDB Interview Questions

MongoDB Practice Questions

MongoDB Tips & Tricks

MongoDB Errors

MongoDB Books

Next.js Tutorial

Free Next.js Tutorial

Next.js Interview Questions

Next.js Practice Questions

Next.js Tips & Tricks

Next.js Errors

Next.js Books

SQL-Constraints 

Pooja Goel  |   Nov 5, 2019  |  Views: 20

Description:

Constraint is used to apply specific rules at time of creating table or after the table is created.

 

There are following types of constraints:


 

1) Unique Key Constraint : (Unique + One Null)  

 

This constraint is used to check for unique values(means No duplicates) . It can allow only one null value.

 

Example : The following SQL creates unique key constraint on column “ID” when you are creating a table

 

CREATE TABLE Employee(
    ID int UNIQUE,
    Name varchar(20),
    Salary Numeric(18,0)
  )

 

Example : The following SQL creates unique key constraint on column “ID” when Table is already created.

 

ALTER TABLE Employee ADD UNIQUE(ID)

 


2) Not Null Constraint : (Non-Null) 

 

Ensures that columns cannot have any Null values.

 

Example : The following SQL creates Not Null constraint on columns “ID” and “Name” when you are creating a table

 

CREATE TABLE Employee(
    ID int NOT NULL,
    Name varchar(20) NOT NULL,
    Salary Numeric(18,0)
  )

Example : 

The following SQL creates Not Null constraint on column “ID” when Table is already created.

 

ALTER TABLE Employee MODIFY Id int NOT NULL


 

3) Primary Key Constraint :(Unique + Not NULL)

 

It is used to identify that all records are unique.

 

Table can have only one Primary Key.

 

Example : The following SQL creates Primary key constraint on column “ID” when you are creating a table  

 

CREATE TABLE Employee(
    ID int PRIMARY KEY,
    Name varchar(20),
    Salary Numeric(18,0)
  )

 

Example : The following SQL creates Primary key constraint on column “ID” when Table is already created.

 

ALTER TABLE Employee ADD PRIMARY KEY (ID)

 


4) Check Constraint : (Limitation for value)

 

This constraint is used to ensure that all the values in a column satisfies certain conditions.

 

Example : The following SQL creates a CHECK constraint on the column “Salary” when you are creating a table.This ensures that you can not have any Employee whose salary is greater than 40000.

 

CREATE TABLE Employee(
    ID int NOT NULL,
    Name varchar(20),
    Salary Numeric(18,0),
CHECK (Salary<=40000)
  )

Example : The following SQL creates check constraint on column “Salary” when Table is already created.

 

ALTER TABLE Employee ADD CHECK (Salary<=40000)


 

5) Default Constraint : (set Default values)

 

It is used to provide default value for a column if no value is specified.

 

Example : The following SQL sets a DEFAULT value for the "Name" column when you are creating a table.

 

CREATE TABLE Employee(
    ID int NOT NULL,
    Name varchar(20) DEFAULT 'Pradesh',
    Salary Numeric(18,0)
  )

 

Example : The following SQL creates DEFAULT constraint on column “Name” when Table is already created.

 

ALTER TABLE Employee MODIFY Name DEFAULT 'Pradesh'

 


6) Foreign Key Constraint : (Hold Reference key)

  

It contain reference of another table.

 

If You have created two tables One is Primary Key table and another table is used to contain reference of  Primary key table so another table will called as “Foreign key Table”

 

If Primary key table is parent table, Foreign key Table will be as Child Table that contain data which present in Primary Key Table.

 

Example : The following SQL creates FOREIGN KEY Constraint for the "DepartmentId" column when you are creating a table “Department”.

 

CREATE TABLE Department(
    ID int  PRIMARY KEY,
    Department Varchar(50),
    DepartmentId int,
    FOREIGN KEY (DepartmentId) REFERENCES Employee(DepartmentId)
)

 

Example : The following SQL creates Foreign key constraint on column “DepartmentId” when Table is already created.

 

ALTER TABLE Department ADD FOREIGN KEY (DepartmentId) REFERENCES Employee(DepartmentId)

 

 

 

 


Comments:
Content Contributor
Pooja Goel

Pooja Goel

Sr. Software developer at Damco Solutions Pvt. Ltd.  |   Noida, Uttar Pradesh, India

I am a sr. software developer working on technologies like SQL, SSIS, SSRS, DB2,Tableau, ASP.NET having 7 years of experience.


Read related articles

SQL-CURSOR

Pooja Goel  |   Nov 16, 2019  |  Views: 13

SQL-Normalization

Pooja Goel  |   Nov 16, 2019  |  Views: 14

SQL-Injection

Pooja Goel  |   Nov 16, 2019  |  Views: 23

SQL-Convert/Cast Function

Pooja Goel  |   Nov 15, 2019  |  Views: 10

SQL-DENSE_RANK()

Pooja Goel  |   Nov 14, 2019  |  Views: 21

SQL-RANK()

Pooja Goel  |   Nov 14, 2019  |  Views: 9

SQL-Row_Number()

Pooja Goel  |   Nov 14, 2019  |  Views: 6

SQL-DATA TYPE

Pooja Goel  |   Nov 11, 2019  |  Views: 17

SQL-String Functions

Pooja Goel  |   Nov 11, 2019  |  Views: 22

SQL- Date Functions

Pooja Goel  |   Nov 11, 2019  |  Views: 16

SQL-TRIGGER

Pooja Goel  |   Nov 11, 2019  |  Views: 12

SQL-CTE

Pooja Goel  |   Nov 10, 2019  |  Views: 12

SQL-Stored Procedures

Pooja Goel  |   Nov 10, 2019  |  Views: 13

SQL-Temporary Tables

Pooja Goel  |   Nov 9, 2019  |  Views: 9

SQL-Merge

Pooja Goel  |   Nov 9, 2019  |  Views: 15

SQL-Subquery

Pooja Goel  |   Nov 9, 2019  |  Views: 57

SQL-EXISTS Operator

Pooja Goel  |   Nov 9, 2019  |  Views: 21

SQL- AND & OR Operator

Pooja Goel  |   Nov 8, 2019  |  Views: 9

SQl-Group by/Order by Clause

Pooja Goel  |   Nov 8, 2019  |  Views: 8

SQL-Where/Having Clauses

Pooja Goel  |   Nov 7, 2019  |  Views: 9

SQL-TOP Clause

Pooja Goel  |   Nov 7, 2019  |  Views: 16

SQL-CASE STATEMENT

Pooja Goel  |   Nov 7, 2019  |  Views: 12

SQL-UNION/UNION ALL

Pooja Goel  |   Nov 7, 2019  |  Views: 9

SQL-COMMENTS

Pooja Goel  |   Nov 6, 2019  |  Views: 45

SQL- ALIAS SYNTAX

Pooja Goel  |   Nov 6, 2019  |  Views: 6

SQL-IS NULL/ NOT NULL

Pooja Goel  |   Nov 6, 2019  |  Views: 13

SQL-IN & BETWEEN

Pooja Goel  |   Nov 6, 2019  |  Views: 10

SQL- DDL, DML, TCL and DCL

Pooja Goel  |   Nov 6, 2019  |  Views: 24

SQL-Aggregate Function

Pooja Goel  |   Nov 5, 2019  |  Views: 9

SQL-Like

Pooja Goel  |   Nov 5, 2019  |  Views: 15

SQL-Constraints

Pooja Goel  |   Nov 5, 2019  |  Views: 21

SQL-Views

Pooja Goel  |   Nov 5, 2019  |  Views: 19

SQL-Indexes

Pooja Goel  |   Nov 5, 2019  |  Views: 10

SQL-JOINS

Pooja Goel  |   Nov 4, 2019  |  Views: 30

SQL-SELECT Statement

Pooja Goel  |   Nov 4, 2019  |  Views: 22

SQL- DELETE Statement

Pooja Goel  |   Nov 4, 2019  |  Views: 25

SQL- UPDATE Statement

Pooja Goel  |   Nov 4, 2019  |  Views: 23

SQL- INSERT Statement

Pooja Goel  |   Nov 4, 2019  |  Views: 33

SQL- DROP / TRUNCATE TABLE

Pooja Goel  |   Nov 4, 2019  |  Views: 36

SQL-CREATE TABLE

Pooja Goel  |   Nov 4, 2019  |  Views: 31

SQL-DROP DATABASE

Pooja Goel  |   Nov 4, 2019  |  Views: 39

SQL-CREATE DATABASE

Pooja Goel  |   Nov 4, 2019  |  Views: 53

SQL-Introduction

Pooja Goel  |   Nov 4, 2019  |  Views: 297

Want to sell your software ?-

If you want to sell your readymade software to the genuine clients or businessman, list your software with details and demo links.

  • Sell ERP
  • Sell Website Clones
  • Sell App Clones
  • Sell Readymade Portals
  • Sell Readymade Websites
  • Sell Mobile Apps
  • Sell Window Apps

Clients will find it using our advanced search filter and will contact you directly.

No any charge for the product lsiting.

Do you own a Company ?+

Property.sale

Buy or Sell Commercial Property+

Are you a freelancer ?+

Looking for a job ?+

Are you a Service Provider ?+

Are you a blogger ?+

Do you own an IT Institute ?+



Property.sale