Strange RI

I am been mucking around with declarative constraints lately and have come across a few weird ones..

This first schema is the only possible way to implement a true one-to-one relationship in SQL Server declaratively
It is such a pity it is impossible to actual add any rows, without issuing a NOCHECK CONSTRAINT.

I will post a solution on how to implement a true one-to-one using security and instead of triggers later

The second is a Self Reference to the same column!

--True One-to-One
CREATE SCHEMA AUTHORIZATION DBO
CREATE TABLE A (Letter CHAR(1) NOT NULL PRIMARY KEY,
   CONSTRAINT FK_A FOREIGN KEY (Letter) REFERENCES B(Letter))
CREATE TABLE B (Letter CHAR(1) NOT NULL PRIMARY KEY,
   CONSTRAINT FK_B FOREIGN KEY (Letter) REFERENCES A(Letter))
Go
--Clean up
ALTER TABLE A DROP CONSTRAINT FK_A
DROP TABLE B,A
GO
--Self Reference to same column
CREATE SCHEMA AUTHORIZATION DBO
CREATE TABLE A (Letter CHAR(1) NOT NULL PRIMARY KEY,
   CONSTRAINT FK_A FOREIGN KEY (Letter) REFERENCES A(Letter))
GO
--Clean up
DROP TABLE A
Can anybody think of others?

Print | posted on Wednesday, November 26, 2003 9:49 AM

Feedback

# re: Strange RI

left by Tim at 11/26/2003 11:00 AM Gravatar
not sure what you mean by "true one to one". What's a real world example?

# re: Strange RI

left by DavidM at 11/26/2003 11:24 AM Gravatar
Tim,

When you declare a One-to-One in SQL Server it is actual a One-to-Zero or One.

A real world example is the Customer to Individual or Customer to Business. The Type- SubType problem...

That is, if you have a Customer row you must have a corresponding Individual row. If implemented using declarative constraints you can have a Customer without an Individual.

This is the example I am going to use.

# re: Strange RI

left by Jeff at 11/27/2003 12:32 AM Gravatar
How about a one-or-the-other constraint?

Table "cust" is the main table of all customers

If customer type=A, then can have 1 matching row in table "TypeA"

If customer type=B, then they can have 1 matching row in table "TypeB".

But, not a row in BOTH. One or the other ! (or neither, I guess -- for the reasons you mention about no true 1-1)

------

create table CustTypes (CustType char(1) primary key)
create table Cust (CustID int primary key,
CustType char(1) references CustTypes(CustType) not null,
constraint PK_alt unique (CustID, CustType))

GO
create table TypeA
(CustID int not null,
CustType char(1) default('A') check (CustType='A'),
constraint TA_PK foreign key (CustID, CustType) references Cust (CustID, CustType))

GO

create table TypeB
(CustID int not null,
CustType char(1) default('B') check(CustType='B'),
constraint TB_PK foreign key (CustID, CustType) references Cust (CustID, CustType))

GO
DROP Table TypeA
DROP TAble TypeB
drop table Cust
Drop table CustTypes

# re: Strange RI

left by DavidM at 11/27/2003 12:53 PM Gravatar
That is useful RI Jeff, not useless like the ones I posted.

I've done that type of constraint but I get that "not in 3NF" feeling.. I ended up creating a view to project away the Type column and never spoke about the column again! Made me feel much better.. :-)

Which raises another question.. Can a view be the target of normalisation and not the base table?

# re: Strange RI

left by Tim at 11/27/2003 1:14 PM Gravatar
ok David, I don't get it obviously, but I am interested in this.

Let's say a person can become a customer.

You can't have a customer without a person, and each person can only be one customer.

Is this "true one to one" or not ? I have included (verbose) script that implements this.

tell me more!


CREATE TABLE [dbo].[Customer] (
[PersonName] [varchar] (50) NOT NULL ,
[CustomerNumber] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Person] (
[PersonName] [varchar] (50) NOT NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] WITH NOCHECK ADD
CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED
(
[PersonName]
) ON [PRIMARY] ,
CONSTRAINT [AK_Customer] UNIQUE NONCLUSTERED
(
[CustomerNumber]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Person] WITH NOCHECK ADD
CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED
(
[PersonName]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[Customer] ADD
CONSTRAINT [FK_Customer_Person] FOREIGN KEY
(
[PersonName]
) REFERENCES [dbo].[Person] (
[PersonName]
)
GO

# re: Strange RI

left by DavidM at 11/27/2003 1:57 PM Gravatar
Tim,

You have implemented the standard One-to-Zero or One RI constraint.

That is, I can Insert into the Person table without having to Insert into the Customer table.

eg. Insert Person (PersonName) values ('Dave')

What we need, is the database to enforce the fact that you MUST have a row in both tables!

Most people simply enforce this rule via Stored Procedures or shock horror, application code.

There is a way to enforce this rule without using Stored Procedures. I will post shortly...

# Implementing a One-To-One Business rule.

left by DavidM at 11/28/2003 12:49 PM Gravatar
Comments have been closed on this topic.