Implementing a One-To-One Business rule.

Fri Nov 28, 2003 by byrmol

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.

--The Database
CREATE DATABASE OneToOne
GO
USE OneToOne
GO
--The Tables
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#))
GO
--The View
CREATE VIEW IndividualCustomer
AS
SELECT C.Customer#, I.FirstName
FROM Customers C
INNER JOIN Individuals I ON I.Customer# = C.Customer#
GO
--The Trigger
CREATE TRIGGER trgIndividual
ON IndividualCustomer
INSTEAD OF INSERT
AS
INSERT Customers (Customer#)
   SELECT Customer# FROM INSERTED
INSERT Individuals (Customer#, FirstName)
   SELECT Customer#, FirstName FROM INSERTED
GO
--Security
EXEC SP_ADDLOGIN 'OneToOne','password', 'OneToOne'
EXEC SP_GRANTDBACCESS 'OneToOne'
GO
DENY INSERT, UPDATE, DELETE ON Customers TO OneToOne
DENY INSERT, UPDATE, DELETE ON Individuals TO OneToOne
GRANT INSERT, SELECT ON IndividualCustomer TO OneToOne
GO
--Now Log In as OneToOne
INSERT Customers (Customer#) VALUES (1)
INSERT Individuals (Customer#, FirstName) VALUES (1, 'Dave')
GO
INSERT IndividualCustomer (Customer#, FirstName) VALUES (1,'Dave')
GO
SELECT * FROM IndividualCustomer
GO
--Clean UP
DROP TRIGGER trgIndividual
DROP VIEW IndividualCustomer,
DROP TABLE Individuals, Customers
EXEC sp_dropUser 'OneToOne'
EXEC sp_dropLogin 'OneToOne'
GO
USE master
GO
DROP DATABASE OneToOne

Problems:
1) The dbo can bypass this mechanism
2) Needs good initial design
3) Is fairly time consuming
4) SQL Server 7 cannot implement this.

Benefits:

Needs good initial design
Is fairly time consuming (Contractors) :-)
Increases the integrity of our data
Provides abstraction from the underlying base tables. Although the view itself does that.
We can perform bulk-load operations without worrying about integrity using the view.

Legacy Comments


Jeff 2003-12-02	re: Implementing a One-To-One Business rule. Very cool as usual, David! I will have to look into your example a little more to see what else we can do to expand on it.

DavidM 2003-12-02	re: Implementing a One-To-One Business rule. I was hoping to "bullying" you into showing the One-To-Either technique.. hence the link!

Jeff 2003-12-03	re: Implementing a One-To-One Business rule. With the "one or the other" constraint, I would imagine you would need 1 view per "sub-table". Each view contains all the common columns in the main "Customer" table, along with the specific columns for that type. then, you just set up one trigger per View and again deny permissions on the tables underneath. Finally, you would set up the constraints between the customer table the sub-tables as I demonstrated so that you would ensure that 1 customer has an entry in only 1 subtable. Would this work?

Jeff 2003-12-03	re: Implementing a One-To-One Business rule. Actually -- if all of the inserts and updates are handled through the "subtable" views, then you probably wouldn't even need use the confusing one-to-either constraints I posted; you could probably just leave as is, since every insert would go into through the View and if more than 1 customer is inserted into different views (and hence different subtables) the insert will fail due to a key violation on the customer table. now was THAT a rambling sentence or what!?

DavidM 2003-12-03	re: Implementing a One-To-One Business rule. That is a very good point Jeff, the PK on the Customers table would indeed take care of that. 2 different RI constraints for the price of one. I like, I like..

Ruya Tabirleri 2008-08-04	re: Implementing a One-To-One Business rule. asdqwdqw