CONSTRAINTS
A constraint is a property assigned to a column or the set of columns in
a table that prevents certain types of inconsistent data values from being placed
in the column(s). Constraints are used to enforce the data integrity.
This ensures the accuracy and reliability of the data in the database.
The following categories of the data integrity exist:
Entity Integrity : Entity Integrity ensures that there are no duplicate rows
in a table.
Domain Integrity : Domain Integrity enforces valid entries for a given column
by restricting the type, the format, or the range of possible values.
Referential integrity : Referential integrity ensures that rows cannot be
deleted, which are used by other records (for example,corresponding data values
between tables will be vital).
User-Defined Integrity : User-Defined Integrity enforces some specific business
rules that do not fall into entity, domain, or referential integrity categories.
Each of these categories of the data integrity can be enforced by the appropriate
constraints.
Microsoft SQL Server supports the following constraints:
PRIMARY KEY
UNIQUE
FOREIGN KEY
CHECK
NOT NULL
A PRIMARY KEY constraint is a unique identifier for a row within
a database table. Every table should have a primary key constraint to uniquely identify
each row and only one primary key constraint can be created for each table. The
primary key constraints are used to enforce entity integrity.
A UNIQUE constraint enforces the uniqueness of the values in a
set of columns, so no duplicate values are entered. The unique key constraints are
used to enforce entity integrity as the primary key constraints.
Each table can have only one primary key. If there are multiple UNIQUE identifiers
for a multiple columns, such column pairs are often referred to as alternate keys
or candidate keys (these terms are not used by SQL Server). In practice, one of
two columns is logically promoted to primary key using the PRIMARY KEY constraint,
and the other is usually declared by a UNIQUE constraint. Internally, PRIMARY KEY
and UNIQUE constraints are handled almost identically.
Here two ways to create a table:
Create table customer(cust_id int IDENTITY
not null PRIMARY KEY, cust_name varchar(30) not null)
Create table customer(cust_id int IDENTITY
not null, cust_name varchar(30) not null, PRIMARY KEY(cust_id))
A FOREIGN KEY constraint prevents any actions that would destroy
link between tables with the corresponding data values. A foreign key in one table
points to a primary key in another table. Foreign keys prevent actions that would
leave rows with foreign key values when there are no primary keys with that value.
The foreign key constraints are used to enforce referential integrity.
A FOREIGN KEY is a column whose values are derived from the PRIMARY KEY or UNIQUE
KEY of some other table.
By using ON DELETE CASCADE option and if a user deletes a record in
the master table, all corresponding recording in the detail table along with the
record in the master table will be deleted.
Create table orders(order_id
int not null PRIMARY KEY, cust_id int not null REFERENCES customer(cust_id) ON DELETE
CASCADE)
A table can have a maximum of 253 FOREIGN KEY references. This limit is derived
from the internal limit of 256 tables in a single query. If you’re dropping tables,
you must drop all the referencing tables or drop the referencing FOREIGN KEY constraint
before dropping the referenced table.
DROP TABLE orders DROP TABLE
customer
A CHECK constraint is used to limit the values that can be
placed in a column. The check constraints are used to enforce domain integrity.
Check constraints allow us to define an expression for a table that must not evaluate
to FALSE for a data modification statement to succeed.
Check constraints deal only with some logical expression for the specific row already
being operated on, so no additional I/O required.
Create table employee(emp_id int not null PRIMARY KEY
CHECK(emp_id between
0 and 1000), emp_name varchar(30) not null constraint no_nums
CHECK(emp_name
not like ‘%[0-9]%’), entered_date datetime null
CHECK(entered_date>=CURRENT_TIMESTAMP),
dept_no int CHECK(dept_no < 0 and dept_no > 100))
A NOT NULL constraint enforces that the column will not accept
null values. The not null constraints are used to enforce domain integrity, as the
check constraints.
A DEFAULT Constraints: A default allows you to
specify a constant value, NULL or the run-time value of a system function if no
known value exists or if the column is missing in an INSERT statement.
Create table employee(emp_id int not null PRIMARY KEY DEFAULT 1000 CHECK(emp_id
between 0 and 1000), emp_name varchar(20) DEFAULT 'Radha Krishna')
Insert into employee values(DEFAULT, DEFAULT)
The order of Integrity checks is as follows:
1. Defaults are applied as appropriate.
2. NOT NULL violations are raised.
3. CHECK constraints are evaluated.
4. FOREIGN KEY checks of referencing tables are applied.
5. FOREIGN KEY checks of referenced tables are applied.
6. UNIQUE/PRIMARY KEY is checked for correctness.
7. Triggers fire.
You can add constraints to an existing table by using the ALTER TABLE statement.
The following example adds a pk_employee primary key constraint on an employee table:
ALTER TABLE employee
ADD CONSTRAINT pk_employee PRIMARY KEY
(EmployeeId)
You can add the primary or unique key constraint into an existing table only when
there are no duplicate rows in the table. You can drop constraints in an existing
table by using the ALTER TABLE statement.
The following example drops the pk_employee primary key constraint in the employee
table:
ALTER TABLE employee
DROP CONSTRAINT pk_employee
Sometimes you need to perform some actions that require the FOREIGN KEY or CHECK
constraints be disabled, for example, your company do not hire foreign employees,
you made the appropriate constraint, but the situation was changed and your boss
need to hire the foreign employee, but only this one.
In this case, you need to disable the constraint by using the ALTER TABLE statement.
After these actions will be performed, you can re-enable the FOREIGN KEY and CHECK
constraints by using the ALTER TABLE statement.
The following example disables the check_sale constraint in the employee table and
enables this constraint later:
-- disable the check_sale constraint in the employee table
ALTER TABLE employee NOCHECK CONSTRAINT check_sale
-- enable the check_sale constraint in the employee table
ALTER TABLE employee CHECK CONSTRAINT check_sale
ALTERING A TABLE: Using the ALTER table command, we can make following types
of changes to an existing table.
We can add only one column for each ALTER TABLE statement.
ALTER TABLE employee
ALTER COLUMN emp_name varchar(50)
ALTER TABLE orders
ADD FOREIGN KEY(cust_id) REFERENCES customer(cust_id)
ALTER TABLE customer
DROP COLUMN cust_name
We cannot drop the columns such as:
A replicated column.
A column used in an index.
A column used in a CHECK, FOREIGN KEY, UNIQUE, or PRIMARY KEY constraint.
A column associated with a default defined with the DEFAULT keyword or bound to
a default object.
A column to which a rule is bound.
The following example is used to create the Books and the Authors
tables and create a foreign key constraint which will perform the cascade delete
action, therefore, when a row in the Authors table is deleted, the corresponding
rows in the Books are also deleted:
CREATE TABLE Books ( BookID INT NOT NULL PRIMARY KEY, AuthorID INT NOT NULL, BookName
VARCHAR(100) NOT NULL, Price MONEY NOT NULL ) GO
CREATE TABLE Authors ( AuthorID INT NOT NULL PRIMARY KEY, Name VARCHAR(100) NOT
NULL ) GO
ALTER TABLE Books ADD CONSTRAINT fk_author FOREIGN KEY (AuthorID) REFERENCES Authors
(AuthorID) ON DELETE CASCADE GO
Conclusion
Constraints are the built-in mechanism for enforcing data integrity. Using constraints
is preferred to using triggers, rules, and defaults because built-in integrity features
use much less overhead and perform faster than the ones you can create. When you
write your own code to realize the same actions the constraints can make you can
make some errors, so the constraints are not only faster, but also are more consistent
and reliable. So, you should use triggers and rules only when the constraints do
not provide all the needed functionality.
No comments:
Post a Comment