SQL Constraints

Constraints are used to limit the type of data that can go into table

Constraints can be specified when table is created (with create table statement) or after

The table is created(With the alter table statement).

We will focus on following constraints:

Not Null Unique PRIMARY KEY FOREIGN KEY CHECK DEFAULT









SQL NOT NULL CONTRAINTS

The NOT NULL constraints enforces a column to NOT accept null values

The NOT NULL constraints enforces a field to always contain a value.this means that you can not insert a new record ,or update a record without adding a value to this field.

create table persons

(

p_id int NOT NULL,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255)

)

INSERT INTO persons (LastName,firstname,address,city)

VALUES (‘Peter’, ‘Hunt’, ‘aaaaa’, ‘udaipur’)

SQL server auto increment

create table persons

(

p_id int primary key identity,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255)

)

INSERT INTO persons ( LastName,firstname,address,city)

VALUES (‘Peter’, ‘Hunt’, ‘aaaaa’, ‘udaipur’)

SQL UNIQUE Constraints

The UNIQUE constraints uniquely identifies each record in database table

The UNIQUE and PRIMARY KEY constraints both provides a guarantee for uniqueness

For column or set of columns

Note: that you can have have many UNIQUE constraints per table, but only one PRIMARY KEY constraints per table.

create table persons

(

p_id int NOT NULL UNIQUE,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255)

)

On multiple columns

create table persons

(

p_id int NOT NULL,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255),

CONSTRAINT uc_personid UNIQUE (p_id,lastname)

)SQL UNIQUE Constraints on ALTER TABLE

To create a UNIQUE constraints on the “P_id” column when the table is already created,use the following

SQLALTER table persons add unique (p_id)

On Multiple column:

ALTER table persons add constraint uc_personid unique (p_id,lastname)

To DROP a UNIQUE constraints

ALTER table persons drop constraint uc_personid

SQL PRIMARY KEY Constraints

The primary key constraints uniquely identifies each record in a database table

Primary key must contain unique values.

Primary key column cannot contain NULL values

Each table can have only one primary key

SQL PRIMARY KEY Constraints On CREATE TABLE

create table persons

(

p_id int NOT NULL PRIMARY KEY,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255),

)

SQL PRIMARY KEY Constraints on multiple column

create table persons

(

p_id int NOT NULL,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255),

CONSTRAINT pk_personid PRIMARY KEY (p_id,lastname)

)

SQL PRIMARY KEY Constraints on ALTER TABLE

Table is already created

Alter table person add primary key (p_id)

On multiple columns

Alter table person add CONSTRAINT pk_personid PRIMARY KEY (p_id,lastname)

Note: If you use the alter table statement to add a primary key column must already have been declared to not contain NULL values (when the table was first created)

To DROP a Primary key constraints

ALTER table persons drop constraint pk_personid

SQL FOREIGN KEY Constraints

A Foreign key in one table point to primary key in anather table

create table persons

(

p_id int not null primary key,

name char(23),

last_name char(23),

address char(34),

city char(34)

)

insert into persons values(‘3′,’Pettersen’,’Kari’,’Storgt 20′,’stavanger’)

insert into persons values(‘1′,’hansen’,’ola’,’Timoteivn 10′,’sandnes’)

insert into persons values(‘2′,’svendson’,’tove’,’borgvn 23′,’sandnes’)

1 Hansen ola timoteivn 10 sandnes 2 Svendson Tove Borgvn 23 sandnes 3 Pettersen Kari Storgt 20 stavanger

create table orders

(

o_id int not null primary key,

orderno int not null,

p_id int foreign key references persons(p_id)

)

O_id orderno p_id

1 77895 3

2 44678 3

3 22456 2

4 24562 1

insert into orders values(‘1′,’77895′,’3’)

insert into orders values(‘2′,’44678′,’3’)

insert into orders values(‘3′,’22456′,’2’)

insert into orders values(‘4′,’24562′,’1’)

note that the “p_id “ column in the “orders” table points column in persons table.

The “p_id” column in “persons” table is the primary key in the “persons table.

The “p_id” column in “orders” table is the foreign primary key in the “orders” table.

SQL FOREIGN KEY Constraints On CREATE TABLE

create table orders

(

o_id int not null primary key,

orderno int not null,

p_id int foreign key references persons(p_id)

)

ON MULTIPLE COLUMNS

create table orders

(

o_id int not null primary key,

orderno int not null,

p_id int,

PRIMARY KEY (o_id),

Constraints fk_perorders foreign key (p_id)

References persons(p_id)

)SQL FOREIGN KEY Constraints on ALTER TABLE

Table is already created

ALTER table orders add foreign key (p_id) references persons (p_id)

On multiple columns

ALTER table orders add constraint fk_perorders foreign key (p_id) references persons (p_id)

To DROP a foreign key constraints

Alter table orders drop constrain fk_perorders

SQL CHECK Constraints

The check constraint is used to limit the values 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 columns

If you define check constraint on table it can limit the values in certain column based n other column in the row.

SQL CHECK Constraint On CREATE TABLE

The following SQL creates a CHECK constraints on the “p_Id” column when the “person” table is created. The CHECK constraint

Specifies that the column “p_id” must only integer greater than 0.

reate table persons

(

p_id int NOT NULL CHECK (p_id>0),

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255),

)

On multiple columns

create table persons

(

p_id int NOT NULL ,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255),

Constraint chk_person check (p_id>0 and city=’sandnes’)

)

SQL CHECK Constraint On Alter TABLE

Alter table persons add check (p_id>0)

On multiple Columns

Alter table persons add constraint chk_person check (p_id>0 and city=’sandnes’)

TO DROP a check Constraint

Alter table persons drop constaint chk_person

SQL DEFAULT Constraint

THE DEFAULT constraint is used to insert a default value into a column.

The default value will be added to all new record, if no other value is specified.

SQL DEFAULT Constraint On CREATE TABLE

create table persons

(

p_id int NOT NULL ,

lastname varchar(255) NOT NULL,

firstname varchar(255),

address varchar(255),

city varchar(255) DEFAULT ‘Sandnes’

)

The default constraint can also be used to insert system values, by using function

Like getdate():

Create table orders

(

O_id int not null,

Orderno int not null,

P_id int,

Orderdate date default getdate()

)SQL DEFAULT Constraint On Alter TABLE

To create a default constraints on the “city” column when the table is already created, use the followings:

Alter table persons alter column city set default ‘SANDNES’

Drop a default constraint

Alter table persons alter column city drop default