Constraints
Primary Key Constraint
Unique Key Constraint
Foreign Key Constraint
Not Null Constraint
Check Constraint
A Constraint is a property that we can
assign to column in table.
What is Primary Key?
Primary key constraint is used to
uniquely identify each record in database table. It won’t allow repetition or
duplication of data. Each table is having only one primary key constraint and
it contains only unique values. Primary key constraint doesn’t accept null
values.
Right click the student table and set primary key
It is look like
Primary key constraint doesn’t accept
null values.
AUTO INCREMENT
Very often we would like the value of
the primary key field to be created automatically every time a new record is inserted.
We would like to create an
auto-increment field in a table.
Query Level
CREATE TABLE
[dbo].[tblStudent](
[StudId]
[int] IDENTITY(1,1) NOT NULL,
[StudName]
[varchar](20) NULL,
[Class]
[int] NULL,
CONSTRAINT [PK_tblStudent] PRIMARY KEY CLUSTERED
(
[StudId]
ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON
[PRIMARY]
) ON
[PRIMARY]
GO
The
primary key created for the StudId column will create a clustered index
for the Studid column. A table can have only one clustered index on
it.
When
creating the clustered index, SQL server 2008 reads the id column and
forms a Binary tree on it. This binary tree information is then stored
separately in the disc. Expand the table Student and then expand the
Indexes. You will see
Unique Key Constraint
Unique Key enforces uniqueness of the
column on which they are defined. Unique Key creates a non-clustered index on
the column. Unique Key allows only one NULL Value.
CREATE TABLE
Persons(P_Id int NOT NULL UNIQUE,LastName varchar(255) NOT NULL,FirstName
varchar(255),Address varchar(255),City varchar(255))
Foreign
key constraint
A Foreign key in one table point to
primary key in another table. The foreign key constraint is used to prevent the
actions that would destroy the links between two tables.
NOT NULL Constraint
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.
Check Constraint
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.
EX:
CREATE TABLE brand (
code char(8) NOT NULL,
valid int NOT NULL,
rowid numeric(10,0)
IDENTITY,
CONSTRAINT brand_pk PRIMARY KEY (code),
-- The following UNIQUE
constraint lets the pair of values be
-- the target of a foreign key reference.
CONSTRAINT brand_is_valid UNIQUE (code, valid),
CONSTRAINT valid_check CHECK
(valid IN (0,1))
);
CREATE TABLE product (
code char(8) NOT NULL,
valid int NOT NULL,
-- The column "code"
is a PK in the referenced table, so this still works. It's
-- a 1:0 or 1:1 relationship.
CONSTRAINT product_pk PRIMARY KEY (code),
-- The next constraint requires
a unique constraint on the pair of
-- columns in the table "brand". By itself, it references every row
-- in "brand". That's too many rows.
CONSTRAINT product_fk FOREIGN KEY
(code, valid)
REFERENCES brand (code, valid),
-- But this constraint restricts
the foreign key references to only those
-- rows that have valid = 1 in the
table "brand".
CHECK (valid = 1)
);
Brand Table
Values
Check Constraint
Table name Brand
CONSTRAINT valid_check CHECK (valid IN
(0,1))
Example Condition :
CHECK (id BETWEEN
100 and 9999)
CHECK (name =
upper(name))
The DELETE statement
conflicted with the REFERENCE constraint "product_fk". The conflict
occurred in database "SampleDB", table "dbo.product".
The statement has
been terminated.
How to delete Brand table value?
- First Delete product
table row.
- After Delete Brand table
value.
