My Profile
Gifts
Active Members
TodayLast 7 Days
more...
|
Database Constraints Part 1
Posted Date: 22 Jul 2008 Resource Type: Articles Category: Databases
|
Posted By: Vijaya Member Level: Diamond
Rating:  Points: 10
|
Overview:
In this article I would like to give you an overview on the constraints in database. This article will also explain what are the different types of constraints and what can you do with these constraints.
Introduction:
Constraints plays very vital role in Database intensive applications. If you want to understand what constraints means in real world is, a big beautiful house with out Doors and Windows. You can imagine what will happen if you don’t have doors and windows to your home. Now let’s get into the business.
The general definition is
“A constraint is a restriction. Placed at either column or table level, a constraint ensures that your data meets certain data integrity rules.”
There are 3 types of constraints in database at a very high level.
1. Entity constraints
Entity constraints are all about individual rows. This kind of constraints doesn't really care about a column value; it's interested in a particular row, and would best be exemplified by a constraint that requires every row to have a unique value for a column or combination of Columns.
Entity constraints are:
1.1) PRIMARY KEY
1.2) UNIQUE
2. Domain constraints
Domain constraints are column level constraints. Either these constraints are based on one or more columns. This is basically to ensure that a particular column or set of columns meets particular criteria.
For example, if we want to confine the Salary column only to values that are greater than zero, that would be a domain constraint. While any Employee that had a Salary that didn't meet the constraint would be rejected, we're actually enforcing integrity to make sure that entire column meets the constraint.
Domain constraints are:
2.1) CHECK
2.2) RULES
2.3) DEFAULTS
3. Referential integrity constraints
Referential integrity constraints are created when a value in one column must match the value in another column — in either the same table or a different table.
Referential Constrains are:
3.1) FOREIGN KEY
Constraint details:
1) PRIMARY KEY
A primary key constraint ensures uniqueness within the columns declared as being part of that primary key, and that unique value serves as an identifier for each row in that table. There are two ways to create a primary key, either in your CREATE TABLE command or with an ALTER TABLE command.
Let's look at the simple example: The below table is to hold Employee information.
CREATE TABLE Employee
(
EmployeeID int IDENTITY NOT NULL,
EmployeeName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Address2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone char(15) NOT NULL
)
Here we want to define primary key on EmployeeID. You may ask why on EmployeeID? Why can't it be on EmployeeName. Well in this big world Names can repeate. So you don’t want to defione primary key on EmployeeName column.
Now if you want to define primary key on EmployeeID column then
CREATE TABLE Employee
(
EmployeeID int IDENTITY NOT NULL
PRIMARY KEY,
EmployeeID int IDENTITY NOT NULL,
EmployeeName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Address2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone char(15) NOT NULL
)
If you don’t want to have this Primary key as part of table script then you can use ALTER statement to define the constraint.
ALTER TABLE Employee
ADD CONSTRAINT PK_EmployeeID
PRIMARY KEY (EmployeeID)
2) UNIQUE KEY
These are relatively simple. UNIQUE Key constraints are essentially the younger sibling of primary keys in that they require a unique value throughout the named column (or combination of columns) in the table. You will often hear UNIQUE Key constraints referred to as alternate keys. The major differences are that they are not considered to be the unique identifier of a record in that table (even though you could effectively use it that way) and that you can have more than one UNIQUE constraint (remember that you can only have one primary key per table).
Once you establish a UNIQUE Key constraint, every value in the named columns must be unique. If you go and update or insert a row with a value that already exists in a column with a unique constraint, SQL Server will raise an error and reject the record.
UNIQUE Key constraint does allow NULL value. Whether NULLs are allowed or not depends on how you set the NULL option for that column in the table. You can have only Null value on a column where Unique Key is defined.
CREATE TABLE Employee
(
EmployeeID int IDENTITY NOT NULL
PRIMARY KEY,
EmployeeID int IDENTITY NOT NULL,
EmployeeName varchar(30) NOT NULL,
Address1 varchar(30) NOT NULL,
Address2 varchar(30) NOT NULL,
City varchar(20) NOT NULL,
State char(2) NOT NULL,
Zip varchar(10) NOT NULL,
Contact varchar(25) NOT NULL,
Phone char(15) NOT NULL UNIQUE
)
If you dont want to have this Unique Key as part of table script then you can use ALTER statement to define the constraint.
ALTER TABLE Employees
ADD CONSTRAINT AK_EmployeePhone
UNIQUE (Phone)
Keep watching for more information in my next article.
|
Responses
|
| Author: ravi 24 Jul 2008 | Member Level: Gold Points : 0 | good stuff keep posting...now i got some clear idea..........
| | Author: Vidhya 06 Aug 2008 | Member Level: Gold Points : 1 | hi,
thanks for sharing ur knowledge
now i got some clear idea aoubt these constraints.
Happy programming!
|
|