Database Reference
In-Depth Information
Unique Constraints
Unique constraints are used to ensure that you have no duplicate values in
a column. Unique constraints can be attached to one or more columns de-
pending on your needs. If you attach a unique constraint to one column,
that single column can contain no duplicated data. If you attach it to mul-
tiple columns, then the combination of data between those columns can-
not be duplicated in another row. In other words, each column involved in
a multicolumn constraint can have duplicates, but when you look at all the
columns together you are not allowed to have repeats.
Unique constraints are used frequently in SQL Server. Mountain View
Music uses them to ensure that key pieces of data are unique—for exam-
ple, order ID. If two customers get the same order ID for different orders,
they might get the wrong product or no product at all. To avoid this, we
add a unique constraint to the ordernumber column in tbl_order.
ALTER TABLE dbo.tbl_order
ADD CONSTRAINT UNQ_ordernumber UNIQUE NONCLUSTERED
(ordernumber)
Often we see uniqueness in tables enforced only for the primary key.
Remember that when you selected your primary key, you evaluated all the
candidate keys in the table. Just because you didn't use a candidate as the
primary key doesn't mean it should be ignored; these columns are great
candidates to have unique constraints.
Using Triggers to Implement Business Rules
Constraints are great, and along with and primary and foreign keys, they go
a long way to enforce a lot of business rules on your data. But what if you
need to implement something a little more advanced, and constraints and
keys just aren't getting the job done? That's where triggers come into play.
Using triggers, you can write custom T-SQL code to run after something
has happened to a table. Triggers can be set up to run after an INSERT,
UPDATE, or DELETE or even instead of one of these actions. Really, the
sky is the limit when you use triggers; you can do almost anything, includ-
ing canceling the statement that fired the trigger.
Keep in mind, however, that triggers fire as part of the transaction that
started them, and they fire each time the action occurs. This means that if
you insert 100,000 rows into a table, an insert trigger would fire 100,000
Search WWH ::




Custom Search