Some of my readers mailed me that,
"Why you write simple article in your web. As you your web contains very good stuff… but some tropics are really simple."
It is facts. I have long list of friends from Linked In web. They send me regular mail to write different types of tropics. From them there is a huge number of junior professional and students. I have to serve them all. Students are a very important reader in my article and I think they really need guideline and I can't ignore them. I also take free class for them in weekends and try to share my knowledge with them. I really like it….
In this article I am trying to illustrate some points related to table constraints. It's not only useful for junior professional but also useful for all readers. So let's starts.
Data integrity rules falls into three categories:
Entity integrity ensures that the each row of the database is uniquely identified. You can specify the PRIMARY KEY constraint in the table.
It ensure that the relationship between tables remain preserved as data is INSERTED, DELETED and MODIFIED.
We can ensure the referential integrity by FOREIGN KEY constraints.
Take an example of "Person" and "Orders" table:
The "Persons" table:
Note that the "P_Id" column in the "Orders" table points to the "P_Id" column in the "Persons" table.
The "Orders" table:
The "P_Id" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "P_Id" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
The FOREIGN KEY constraint also prevents that invalid data form being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.
( P_id int NOTNULL PRIMARYKEY,
LastName Varchar(50) NOTNULL,
FirstName Varchar(50) NOTNULL,
Address Varchar(100) NULL,
City Varchar(50) NULL
( O_Id int NOTNULLPRIMARYKEY,
OrderNo int NOTNULL,
P_Id int FOREIGNKEY REFERENCES Persons(P_Id)
If the table objects are already created we can use the Alter statements to set the foreign key.
Also we can use CASCADE DELETE with referential integrity. In the example if we delete a specified person from the person table the corresponding Order will be automatically deleted.
We can also use CASCADE UPDATE with referential integrity. CASCADE specifies that the column will be updated when the referenced column is updated.
You can check if any foreign key is defined in your database with cascading actions usingsys.foreign_keys
SELECT name AS [Constraint Name],
OBJECT_NAME(referenced_object_id) [Referenced Object],
OBJECT_NAME(parent_object_id) [Parent Object],
delete_referential_action_desc [ON DELETE],
update_referential_action_desc [ON UPDATE]
Domain integrity ensures that the values inside a database follow defined rules of values, range and format. Database can enforce the rules using
1. CHECK Constraints
2. UNIQUE Constraints
3. DEFAULT Constraints
Check constraints contain an expression the database will evaluate when we modify or insert a row. If the expression evaluates to false, the database will not save the row. Building a check constraint is similar to building a WHERE clause. You can use many of the same operators (>, <, <=, >=, <>, =) in additional to BETWEEN, IN, LIKE, and NULL. You can also build expressions around AND and OR operators. We can use check constraints to implement business rules, and tighten down the allowed values and formats allowed for a particular column.
ProductID int PRIMARYKEY,
UnitPrice money CHECK(UnitPrice > 0 AND UnitPrice < 100)
ADD CONSTRAINT CK_HireDate CHECK(hiredate <GETDATE())
The UNIQUE Constraints
The unique constraints determine that the columns values must be unique. Only one NULL is allowed by UNIQUE constraints.
ProductID int PRIMARYKEY,
ProductName nvarchar(40) Constraint IX_ProductName UNIQUE
ADD CONSTRAINT IX_ProductName UNIQUE(ProductName)
Default constraints apply a value to a column when an INSERT statement does not specify the value for the column. Although default constraints do not enforce a rule like the other constraints we have seen, they do provide the proper values to keep domain integrity intact. A default can assign a constant value, the value of a system function, or NULL to a column. You can use a default on any column except IDENTITY columns and columns of type timestamp.
OrderID int IDENTITY NOTNULL,
EmployeeID int NOTNULL,
OrderDate datetime NULL DEFAULT(GETDATE()),
Freight money NULL DEFAULT(0)CHECK(Freight >= 0),
ShipAddress nvarchar(60) NULL DEFAULT('NO SHIPPING ADDRESS'),
EnteredBy nvarchar(60) NOTNULL DEFAULT(SUSER_SNAME())
After creating the constraint, if you think that the you don't needed the constraints you can drop the constraints or disable/enable the constraints.
To DROP the constraints
ALTERTABLE Products NOCHECKCONSTRAINT CK_UnitsOnOrder
ALTERTABLE Products NOCHECKCONSTRAINT ALL
ALTERTABLE Products CHECKCONSTRAINT CK_UnitsOnOrder
ALTERTABLE Products CHECKCONSTRAINT ALL
Hope you like it.
Posted by: MR. JOYDEEP DAS