SQL Server can only implement a One-To-Zero Or One relationship.
When we attempt a One-To-One declaritevly, it turns out to be useless. Strange RI
Unfortunately, most real world cases need a true One-To-One Constraint.
As an example I'm going to use the Customer to Individual example.
That is, a Customer can be an Individual or some other "type".
But the Customer entity MUST have a corresponding row in the Individuals entity.
Usually we have other "types" of customers such as businesses or charities, but to be succinct I am only going to use the Individual.
I am hoping Jeff Smith
will post an extension to this example to show how to implement a One-To-Either constraint when we add other "types".
It is usually referred to as the Type-SubType, but what they really mean is Super/Sub Tables
What the hell is a Super/Sub Table?.
I have no idea!! Strongly Related Entity is probably more accurate but I just made that up!
In our context this is NOT a OO design principal but is simple a consequence of good normalisation practice from the given business requirement.
Use Eiffel/C#/Java etc..and turn your middle tier into a OO paradise but leave it behind when you enter the relational world.
Enough DB nazi rant, lets get to it...
So how do we do it?
The most common implmentation is via Stored Procedures. The user is exposed to a SP that handles the logic.
A problem with this method is when you have to expose your database to bulk insert operations.
Database integrity is at the mercy of the bulk loader. Customers without a correspong Individual entity can appear.
The solution below is only valid for SQL Server 2000 as it involves the use of INSTEAD OF triggers on views.
The view IndividualCustomer is the join of Customers and Individuals, abstracting away the fact there are 2 underlying tables.
The INSTEAD OF trigger is created on this view and handles the logic.
I have created the view with multi-row inserts in mind to cover any set based insertion.
The next vital step is security.
The User (OneToOne) is denied permission to alter the 2 base tables, but is allowed to insert into the view.
Thankfully SQL Server is smart enough to tell the very important difference even though the trigger inserts into the base tables.
Although by now you are probably asking what is the difference between logic in a SP and that of the view?
Well to be honest not much except that we do not need transaction handling in the view trigger as SQL Server considers it to be an atomic operation.
CREATE DATABASE OneToOne
CREATE TABLE Customers(Customer# INT NOT NULL PRIMARY KEY)
CREATE TABLE Individuals(Customer# INT NOT NULL PRIMARY KEY, FirstName VARCHAR(25) NOT NULL,
CONSTRAINT FK_IndividualCustomer FOREIGN KEY (Customer#) REFERENCES Customers (Customer#))
CREATE VIEW IndividualCustomer
SELECT C.Customer#, I.FirstName
FROM Customers C
INNER JOIN Individuals I ON I.Customer# = C.Customer#
CREATE TRIGGER trgIndividual
INSTEAD OF INSERT
INSERT Customers (Customer#)
SELECT Customer# FROM INSERTED
INSERT Individuals (Customer#, FirstName)
SELECT Customer#, FirstName FROM INSERTED
EXEC SP_ADDLOGIN 'OneToOne','password', 'OneToOne'
EXEC SP_GRANTDBACCESS 'OneToOne'
DENY INSERT, UPDATE, DELETE ON Customers TO OneToOne
DENY INSERT, UPDATE, DELETE ON Individuals TO OneToOne
GRANT INSERT, SELECT ON IndividualCustomer TO OneToOne
--Now Log In as OneToOne
INSERT Customers (Customer#) VALUES (1)
INSERT Individuals (Customer#, FirstName) VALUES (1, 'Dave')
INSERT IndividualCustomer (Customer#, FirstName) VALUES (1,'Dave')
SELECT * FROM IndividualCustomer
DROP TRIGGER trgIndividual
DROP VIEW IndividualCustomer,
DROP TABLE Individuals, Customers
EXEC sp_dropUser 'OneToOne'
EXEC sp_dropLogin 'OneToOne'
DROP DATABASE OneToOne
1) The dbo can bypass this mechanism
2) Needs good initial design
3) Is fairly time consuming
4) SQL Server 7 cannot implement this.
1) Needs good initial design
2) Is fairly time consuming (Contractors) :-)
3) Increases the integrity of our data
4) Provides abstraction from the underlying base tables. Although the view itself does that.
5) We can perform bulk-load operations without worrying about integrity using the view.
| posted on Friday, November 28, 2003 12:49 PM