DavidM

not sure what you mean by "true one to one". What's a real world example?

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.

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

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?

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

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...