CONSTRAINTS IN SQL SERVER 2005
You can place constraints 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.
KEY WORD DESCRIPTION
——————————————————————————————————
1. NOT NULL - TO AVOID NULL VALUES, BUT DUPLICATE VALUES ALLOWED
2. UNIQUE - TO AVOID DUPLICATE VALUES, BUT NULL VALUES ALLOWED
3. DEFAULT - TO INSERT A DEFAULT VALUE IN CASE NO VALUE SUPPLIED BY USER
4. CHECK - TO CHECK A SPECIFIC CONDITION, SPECIFIED AT TABLE DESGIN TIME
5. PRIMARY KEY - COMBINATION OF NOT NULL AND UNIQUE
6. FOREIGN KEY - PRIMARY KEY OF ONE TABLE USED IN ANOTHER TABLE
Custom Search
EXAMPLES: -
NOT NULL
By default, a column can hold NULL. If you not want to allow NULL value in a column, you will want to place a constraint on this column specifying that NULL is now not an allowable value.
For example, in the following statement,
CREATE TABLE EMPLOYEE
(
Id int NOT NULL,
Name varchar (30) ,
Age int
)
Column "Id" cannot include NULL, while "Name" and "Age" can include NULL.
* Column "Id" can include duplicate values.
UNIQUE
The UNIQUE constraint ensures that all values in a column are distinct. Duplicate values are not allowed.
For example, in the following statement,
CREATE TABLE EMPLOYEE
(
Id int UNIQUE,
Name varchar (30) ,
Age int
)
Column "Id" cannot include duplicate values, while such constraint does not hold for columns "Name" and "Age".
* Please note that a column that is specified as a primary key must also be unique. At the same time, a column that's unique may or may not be a primary key.
DEFAULT
A column can be assigned a default value. When a new row is created and no values are specified for some of the columns, the columns will be filled with their respective default values.
If no default value is declared explicitly, the null value is the default value. This usually makes sense because a null value can be thought to represent unknown data.
For example, in the following statement,
CREATE TABLE EMPLOYEE
(
Id int ,
Name varchar (30) ,
Age int DEFAULT 18
)
CHECK
The CHECK constraint ensures that all values in a column satisfy certain conditions.
For example, in the following statement,
CREATE TABLE EMPLOYEE
(
Id int ,
Name varchar (30) ,
Age int CHECK( Age > 18)
)
Column "Age" must only include integers greater than 18.
* NULL and Duplicate values are allowed.
Primary Key
A primary key is used to uniquely identify each row in a table. A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is created (using CREATE TABLE) or by changing the existing table structure (using ALTER TABLE).
Below are examples for specifying a primary key when creating a table and using alter table:
CREATE TABLE EMPLOYEE
(
Id int PRIMARY KEY,
Name varchar (30) ,
Age int CHECK( Age > 18)
)
OR USING ALTER TABLE
ALTER TABLE EMPLOYEE ADD PRIMARY KEY (Id)
FOREIGN KEY
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.
For example, say we have two tables, a TEACHER table that includes data about teacher, and an STUDENT table that includes all student records. The constraint here is that all students records must be associated with a teacher that is already in the TEACHER table. In this case, we will place a foreign key on the STUDENT table and have it relate to the primary key of the TEACHER table. This way, we can ensure that all student records in the STUDENT table are related to a teacher in the TEACHER table. In other words, the STUDENT table cannot contain information of a teacher that is not in the TEACHER table.
CREATE TABLE TEACHER
(
tid int primary key,
tname varchar(50)
)
CREATE TABLE STUDENT
(
sid int primary key,
sname varchar(50),
tid int foriegn key references TEACHER(tid)
)
To enable automatic delete and update queries in STUDENT table when a row will delete from TEACHER table, include 'on delete set NULL' and 'on update cascade'.
CREATE TABLE TEACHER
(
tid int primary key,
tname varchar(50)
)
CREATE TABLE STUDENT
(
sid int primary key,
sname varchar(50),
tid int foriegn key references TEACHER(tid) on delete set NULL on update cascade
)
hhhhhhhhhhhhhhhhhhhhhhhhhhhhh.............
Reply to this
HYE.............
Reply to this