 |
|
SQL Server Tips by Burleson |
Enforcing referential integrity
The first step in defining relationships between tables is to decide
which columns will be primary keys and which will be foreign keys.
The next step is to consider whether to cascade or not the INSERT,
DELETE or UPDATE operations.
DRI example
For the next examples the table and columns’ names have spaces,
which is legal in SQL and it is common in real world applications.
The downside is that it limits database migration between vendors.
Object names with spaces must have brackets. For most programmers
new to database systems, who use underscore to denote a space, this
might look strange at the beginning but the concept is easily
assimilated.
For this example it will be necessary to create a table with a
primary key:
CREATE TABLE [Tbl A] ([col A] [varchar]
(50) NOT NULL)
GO
ALTER TABLE [Tbl A] ADD CONSTRAINT
[PK_Tbl A] PRIMARY KEY CLUSTERED ([Col A])
And another table with a foreign key:
CREATE TABLE [Tbl B] ([Col B] [varchar]
(50))
GO
ALTER TABLE dbo.[Tbl B] WITH NOCHECK ADD CONSTRAINT
[FK_Tbl B_Tbl A] FOREIGN KEY
([Col B]) REFERENCES[Tbl A]([Col A]) ON UPDATE CASCADE
ON DELETE CASCADE
Notice the cascade delete and update, they are both optional. The
default cascade insert cannot be defined in the foreign key because
it is implicit. It is not possible to have cascade delete or update
without insert. The only way to prevent cascade insert is to disable
the FOREIGN KEY constraint using the NOCHECK clause:
ALTER TABLE dbo.[Tbl B]
NOCHECK CONSTRAINT [FK_Tbl B_Tbl A]
To reactivate a deactivated constraint is done with the CHECK
clause:
ALTER TABLE dbo.[Tbl B]
CHECK CONSTRAINT [FK_Tbl B_Tbl A]
What is the use of having a constraint and not using? It might be
necessary later on or it might be activated or deactivated by code.
If it is really necessary to remove it, the DROP clause will do
that:
ALTER TABLE dbo.[Tbl B]
DROP CONSTRAINT [FK_Tbl B_Tbl A]
This will permanently delete the constraint and, if needed again, it
will have to be recreated using ALTER TABLE.
The above book excerpt is from:
Super SQL
Server Systems
Turbocharge Database Performance with C++ External Procedures
ISBN:
0-9761573-2-2
Joseph Gama, P. J. Naughter
http://www.rampant-books.com/book_2005_2_sql_server_external_procedures.htm |
