articles

Home / DeveloperSection / Articles / SQL Constraints

SQL Constraints

Anonymous User11714 11-Jul-2011

Constraints are used to limit the type of data that can go into a table. Such constraints can be specified when the table is first created via the CREATE TABLE statement, or after the table is already created via the ALTER TABLE statement. That is we can say that through the constraints we have defines that which type of data accepted by the table column.

Generally there is lots of variety of constraints in which some important constraints are given below:

  1.       Not Null constraint
  2.       Default constraint
  3.       Unique constraint
  4.       Check constraint
  5.       Primary key constraint
  6.       Foreign key constraint
  7.       Null  constraint
Not Null Constraints:

Not Null Constraints ensures that a column can’t have   null value. That is Not Null Constraints restrict to column to accept null values. The NOT NULL Constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field.

Example:

Creating a table name ‘TEST_ Constraints’ which two filed have not null values. Let’s see the following code.

------- Demonstration of NOT NULL CONSTRIANTS
 CREATE TABLE TEST_CONSTRAINTS
 (
      ID INT NOT NULL,        ----// CREATE NOT NULL CONSTRAINTS
      NAME VARCHAR(20) NOT NULL,    ----// CREATE NOT NULL CONSTRAINTS
MOBILENO    VARCHAR(11)
 
 )
----ID AND NAME COLUMN CAN’T HAVE NULL VALUES.
Example: Alter Command for not null constraints
--- SYNTAX DEMONSTRATION OF ALTER COMMAND FOR NOT NULL CONSTRAINT
ALTER TABLE <TABLE_NAME> ALTER COLUMN <COLUMN_NAME>  <COLUMN_DATA_TYPE> NOT NULL
--- DEMONSTRATION OF ALTER COMMAND FOR NOT NULL CONSTRAINT
ALTER TABLE STUDENT ALTER COLUMN ID  INT NOT NULL
Default Constraints:

The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified. Default constraints provide default value when none is specified.

Example:
------ DEMONSTRATION OF DEFAULT CONSTRAINTS
CREATE TABLE TEST_DEFAULTCONSTRAINTS
(
 ID INT NOT NULL,
 NAME VARCHAR(20) NOT NULL,
 MOBILNO VARCHAR(11) DEFAULT '8953690372'  --- DEFAULT CONSTRAINTS VALUE
 
)

Here MOBILNO is default constraints column when no value is specified for this column then default value is inserted in this column.

Example:
--- SYNTAX DEMONSTRATION OF ALTER COMMAND FOR DEFAULT CONSTRAINT
ALTER TABLE <TABLE_NAME> ADD CONSTRAINT <CONSTRAINT_NAME> DEFAULT <DEFAULT_VALUE> FOR <COLUMN_NAME>
--- SYNTAX DEMONSTRATION OF ALTER COMMAND FOR DEFAULT CONSTRAINT
ALTER TABLE STUDENT ADD CONSTRAINT df_name DEFAULT 'arun singh' FOR name
Unique Key Constraints:

Unique Key Constraints guaranteed to uniqueness value in column. Unique key and primary key both are provide the uniqueness of value in a column but they are quite different in some manner, more than one unique   key can be created in table where as only one primary can be created in a table.

Example: 
 ------DEMONSTRATION OF UNIQUE KEY CONSTRAINTS
CREATE TABLE TEST_UNIQUECONSTRAINTS
(
  ID INT NOT NULL,
  NAME VARCHAR(20),
  MOBILENO VARCHAR(11),
  UNIQUE(ID)  ----------CREATE UNIQUE KEY ON ID COLUMN
 
) 
Example:
--- SYNTAX DEMONSTRATION OF ALTER COMMAND FOR UNIQUE KEY CONSTRAINT
ALTER TABLE <TABLE_NAME> ADD UNIQUE <[COLUMN_NAME1],[COLUMN_NAME2],...>
--- DEMONSTRATION OF ALTER COMMAND FOR UNIQUE KEY CONSTRAINT
ALTER TABLE STUDENT ADD  UNIQUE (NAME )
Check Constraints:

The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

Check constraints ensures that all values in column satisfy certain condition. Suppose that we want to enter that person age whose age is greater than 20 then there we have to apply check constraint. Let’s see an example.

Example:
-------- DEMONSTRATION OF CHECK CONSTRAINTS
CREATE TABLE TEST_CHECKCONSTRAINTS
(
   ID INT NOT NULL,
   NAME VARCHAR(20),
   MOBILENO VARCHAR(11),
   AGE INT CHECK (AGE >20 ),  -------- CREATE CHECK CONSTRAINT AGE IS GREATER THAN 20
   CONSTRAINT PK_ID PRIMARY KEY(ID)       ----- CREATE PRIMARY KEY ON ID COLUMN
 
)
Example:
--- SYNTAX DEMONSTRATION OF ALTER COMMAND FOR CHECK CONSTRAINT
 ALTER TABLE <TABLE_NAME> ADD  CONSTRAINT <CONSTRAINT_NAME> CHECK (<CONDITION>)
--- DEMONSTRATION OF ALTER COMMAND FOR CHECK CONSTRAINT
ALTER TABLE STUDENT ADD CONSTRAINT CHK_ID CHECK (ID>0)
Primary key constraint:

Primary key are those key which are uniquely defined in a database table or we can say that a database table can contain only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself, or it can be an artificial field (one that has nothing to do with the actual record).

The Primary Key constraint uniquely identifies each record in a database table. Primary keys must contain unique values. A primary key column cannot contain null values. Each table should have a primary key, and each table can have only one primary key.

Example: Creating primary key on column in a table

 There are two ways through which we have to create primary key on any column within a table.

First way to creating primary key:
 --------DEMONSTRATION OF CREATING PRIMARY KEY
CREATE TABLE TEST_PRIMARYcONSTRATION
(
 ID INT NOT NULL PRIMARY KEY, ------ CREATING PRIMARY KEY SYNTAX ON COLUMN ID
 NAME VARCHAR(50),
 MOBILNO VARCHAR(11),
 [ADDRESS] VARCHAR(50)
 
)
Second way to creating primary key
------ DEMONSTRATION OF CREATING PRIMARY KEY FROM SECOND WAY
CREATE TABLE TEST_PRIMARYCONSTRAINTS
(
ID INT NOT NULL,
NAME VARCHAR(20),
MOBILENO VARCHAR(11),
CONSTRAINT PK_ID PRIMARY KEY (ID) -----CREATING PRIMARY KEY WITH CONSTRAINTS NAME PK_ID ( YOU CAN USE YOUR OWN CONSTRAINT NAME)
 
)
Example: Drop Primary Key on a Table
 -------DDEMONSTRATION OF DROP PRIMARY KEY
ALTER TABLE TEST_PRIMARYcONSTRATION
DROP CONSTRAINT CK_ID ------DROP PRIMARY KEY
Example: Primary Key on Alter Table

 

 ------DEMONSTRATION OF PRIMARY KEY ON ALTER TABLE
ALTER TABLE TEST_CONSTRAINTS1
ADD PRIMARY KEY(ID)     ---- ADD PRIMARY KEY

NOTE:In case of creating primary key on alter table command you must be careful on which column you want create primary key that column neither contains null value nor duplicate value”.

Foreign Key Constraint: A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

A Foreign Key in one table points to a Primary Key in another table. Let’s illustrate the foreign key with an example. Look at the following two tables:

FIRST TABLE WHICH HAS PRIMARY KEY
------ DEMONSTRATION OF CREATING PRIMARY KEY FROM SECOND WAY
CREATE TABLE TEST_PRIMARYCONSTRAINTS
(
ID INT NOT NULL,
NAME VARCHAR(20),
MOBILENO VARCHAR(11),
CONSTRAINT CK_ID PRIMARY KEY (ID) -----CREATING PRIMARY KEY WITH CONSTRAINTS NAME CK_ID 
)

 SECOND TABLE WHICH HAS FOREIGN KEY WITH REFERENCES OF FIRST TABLE PRIMARY KEY

 -----DEMONSTRATION OF CREATING FOREIGN KEY
CREATE TABLE TEST_FOREIGNCONSTRAINTS
(
ID INT NOT NULL FOREIGN KEY REFERENCES TEST_PRIMARYCONSTRAINTS(ID),------- CREATING FOREIGN KEY WITH REFERENCES PRIMARY KEY OF TEST_PRIMARYCONSTRAINTS TABLE ID COLUMN
NAME VARCHAR(20),
MOBILENO VARCHAR(11)
 
)
 Null Constraint:

Null constraints ensures that when no value specified for the column then SQL server provide NULL as default value of column. For NULL constraint there is no any specific line of code to add with column.

Example:
 -----DEMONSTRATION OF NULL CONSTRAINTS
CREATE TABLE TEST_NULLCONSTRAINT
(
--------HERE NO ANY SPECIFIC LINE OF CODE TO ADD WITH COLUMN FOR SPECIFYING NULL CONSTRAINTS
ID INT ,
NAME VARCHAR(20),
MOBILENO VARCHAR(50),
[ADDRESS] VARCHAR(50)
)
Composite Key:

Composite key is a combination of more than one column to uniquely identify row of table. That is composite key is a primary key that consist of more than one column to uniquely identify table.

Example: Creating Composite Key with create command
 ----DEMONSTRATION OF CREATING COMPOSITE KEY WITH CTREATE TABLE COMMAND-----
CREATE TABLE TEST_COMPOSITE
(
 ID INT NOT NULL,
 [UID] INT NOT NULL,
 NAME VARCHAR(20)
 CONSTRAINT CK_TEST_COMPOSITE PRIMARY KEY (ID,[UID])
)
Example: Creating composite key with alter table command
 ----DEMONSTRATION OF CREATING COMPOSITE KEY WITH ALTER TABLE COMMAND-----
ALTER TABLE TEST_COMPOSITE ADD CONSTRAINT CM_TEST_COMPOSITE PRIMARY KEY (ID,[UID])
Example:Drop composite key with alter table command
 ----DEMONSTRATION OF DROPPING COMPOSITE KEY WITH ALTER TABLE COMMAND-----
ALTER TABLE TEST_COMPOSITE DROP CONSTRAINT  CM_TEST_COMPOSITE



Updated 04-Mar-2020
I am a content writter !

Leave Comment

Comments

Liked By