In my Previous articles we talked about Primary Key and Unique Constraints.
If you haven't got a chance then use the below link.
http://www.dotnetspider.com/resources/ViewResource.aspx?resourceId=19705
In this articles, i would like to discuss/focus on rest of the constraints.
3) CHECK Constraint
The good thing about CHECK constraints is that they are not restricted to a particular column. They can be defined on multiple columns as long as all the columns are within a single or same table. So you can define the CHECK constraint at the column level and also at the table level.
Defining this kind of constraint is very similar to that of having WHERE conditions in the query. If you can think of, you can have WHERE conditions to all the DML statements except one i.e. INSERT. So CHECK constraints are WHERE conditions to the INSERT statement. The only thing is instead of having WHERE condition in the INSERT query you are having this condition defined at the table level or column level.
Let’s look at the examples:
Scenario # 1
1) Employee Salary must be positive --> SALARY >= 0
Scenario # 2
1) Proper SSN formatting --> LIKE '[0-9][0-9][0-9]-[0-9] [0-9]-[0-9][0-9][0-9][0-9]'
Scenario # 3
1) Referencing another column in the same row --> DateOfJoin >= DateOfBirth
Let's add one column to our Employee:
ALTER TABLE Employee ADD SALARY INT;
Let’s add constraint on this column:
ALTER TABLE Employee
ADD CONSTRAINT CK_Salary
CHECK
(Salary >= 0);
Now try to insert a record that violates the CHECK constraint; you'll get an error:
INSERT INTO Employee
(EmployeeName, Address1, Address2, City, State, Zip, Contact,
Phone, Salary)
VALUES
('Employee1', 'Address1', 'Add2', 'MyCity', 'NY', '55555',
'No Contact', -1);
Msg 547, Level 16, State 0, Line
1 The INSERT statement conflicted with the CHECK constraint "CK_Salary".
The conflict occurred in database "Pubs", table "dbo.Employee", column
'Salary'.
The statement has been terminated.
4) DEFAULT Constraint
you have defined the default constraint. Based on the column data type you can have value like 0 for Number/int Columns and value like 'NOT PROVIDED' for string columns.
The main things to understand about a DEFAULT constraint are that:
1) Defaults are only used in INSERT statements — they are ignored for UPDATE and DELETE statements.
2) If any value is supplied in the INSERT, then the default is not used.
3) If no value is supplied, the default will always be used.
Let's add one column to our Employee:
ALTER TABLE Employee ADD SALARY INT;
Let’s add constraint on this column:
ALTER TABLE Employee
ADD CONSTRAINT DF_Salary
DEFAULT 0 FOR Salary
Now when you execute the below statement
INSERT INTO Employee
(EmployeeName, Address1, Address2, City, State, Zip, Contact,
Phone)
VALUES
('Employee1', 'Address1', 'Add2', 'MyCity', 'NY', '55555',
'No Contact');
5) FOREIGN KEY
Foreign keys are both a method of ensuring data integrity and a manifestation of the relationships between tables. When you add a foreign key to a table, you are creating a dependency between the table for which you define the foreign key and the table your foreign key references. After adding a foreign key, any record you insert into the referencing table must either have a matching record in the referenced column(s) of the referenced table, or the value of the foreign key column(s) must be set to NULL.
Let's add new table Employee_Sal_Hist which contains all the infomration w.r.t salary.
CREATE TABLE Employee_Sal_Hist
(
EmployeeID int NOT NULL FOREIGN KEY REFERENCES Employee(EmployeeID),
Old_Salary int,
New_Salary int
)
Adding a Foreign Key to an Existing Table:
Just like with primary keys, or any constraint for that matter, we have situations where we want to add our foreign key to a table that already exists. This process is similar to creating a primary key.
ALTER TABLE Employee_Sal_Hist
ADD CONSTRAINT FK_Employee_Sal_Hist
FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)
Summary
Happy Coding....
|
| Author: ravi 24 Jul 2008 | Member Level: Gold Points : 0 |
thnx for posting
|
| Author: Nirupa Rani Acharya 27 Jul 2008 | Member Level: Bronze Points : 0 |
give code of stored procedure pleaseeeee...
|