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-OneCREATE 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?
Legacy Comments
|
Tim
2003-11-26 |
re: Strange RI not sure what you mean by "true one to one". What's a real world example? |
|
DavidM
2003-11-26 |
re: Strange RI 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. |
|
Jeff
2003-11-27 |
re: Strange RI 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 |
|
DavidM
2003-11-27 |
re: Strange RI 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? |
|
Tim
2003-11-27 |
re: Strange RI 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 |
|
DavidM
2003-11-27 |
re: Strange RI 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... |