Circular FK Constraints

Hi.
My system includes Clients and Contacts in a many-to-many relationship
handled in the usual way using a link table with a primary key of Client key
plus Contact key and foreign key constraints against Client and Contact
tables. Each Client may have a Main Contact, which is handled at present with
a 'Main Contact' column in the Client table, which for referential integrity
has a foreign key constraint against the link table. These circular FK
constraints are a nuisance when it comes to deleting a client. I suppose I
could use a trigger to enforce referential integrity, but I don't want to -
the Client table already has a rather complicated trigger.
Can anyone think of a better way?
Thanks.
Peter Hyssett
Peter Hyssett wrote:
> Hi.
> My system includes Clients and Contacts in a many-to-many relationship
> handled in the usual way using a link table with a primary key of Client key
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present with
> a 'Main Contact' column in the Client table, which for referential integrity
> has a foreign key constraint against the link table. These circular FK
> constraints are a nuisance when it comes to deleting a client. I suppose I
> could use a trigger to enforce referential integrity, but I don't want to -
> the Client table already has a rather complicated trigger.
> Can anyone think of a better way?
> Thanks.
Well, in my mind, your main contact fk would be better off referencing
the contact table instead of the link table.
When deleting a contact, you would obviously need to either set the
client.main contact column to a valid id from the contact table first,
or set it to null whichever is appropriate.
Then you should be fine.
JB
|||Thanks. I'm afraid the FK is against the link table because the main contact
must be a contact already linked to the client, which an FK against the
Contact table would not enforce. Nowadays I do set the Main Contact column to
NULL before deleting (the link table rows being deleted first), but I would
prefer not to.
Peter Hyssett
"John B" wrote:

> Peter Hyssett wrote:
> Well, in my mind, your main contact fk would be better off referencing
> the contact table instead of the link table.
> When deleting a contact, you would obviously need to either set the
> client.main contact column to a valid id from the contact table first,
> or set it to null whichever is appropriate.
> Then you should be fine.
> JB
>
|||Hello, Peter
As I understand this, your DDL is (or should be) something like this:
CREATE TABLE Contacts (
ContactID int PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns...
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns...
MainContactID int NULL
)
CREATE TABLE ClientContacts (
ClientID int REFERENCES Clients ON DELETE CASCADE,
ContactID int REFERENCES Contacts ON DELETE CASCADE,
PRIMARY KEY (ClientID, ContactID)
)
ALTER TABLE Clients ADD CONSTRAINT TheFK
FOREIGN KEY (ClientID, MainContactID)
REFERENCES ClientContacts (ClientID, ContactID)
Assuming the following sample data:
SET NOCOUNT ON
INSERT INTO Clients (ClientID, ClientName)
VALUES (100, 'Big Company, Inc.')
INSERT INTO Contacts VALUES (1, 'John', 'Smith')
INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
INSERT INTO ClientContacts VALUES (100, 1)
INSERT INTO ClientContacts VALUES (100, 2)
UPDATE Clients SET MainContactID=1 WHERE ClientID=100
INSERT INTO Clients (ClientID, ClientName)
VALUES (200, 'Another Company, Ltd.')
INSERT INTO Contacts VALUES (3, 'John', 'Doe')
INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
INSERT INTO ClientContacts VALUES (200, 2)
INSERT INTO ClientContacts VALUES (200, 3)
INSERT INTO ClientContacts VALUES (200, 4)
UPDATE Clients SET MainContactID=3 WHERE ClientID=200
SET NOCOUNT OFF
Let's suppose you want to delete the client with the ClientID=100.
Using a simple "DELETE Clients WHERE ClientID=100" works (without any
error): the client is deleted and it's links with the contacts, too
(but the contacts themselves remain). If you want to also delete the
contacts (the ones that are not linked with any other client), you can
use the following trigger:
CREATE TRIGGER Clients_DeleteContacts ON Clients
INSTEAD OF DELETE
AS
IF @.@.ROWCOUNT>0 BEGIN
SET NOCOUNT ON
UPDATE Clients SET MainContactID=NULL
WHERE ClientID IN (SELECT ClientID FROM deleted)
DELETE Contacts WHERE ContactID IN (
SELECT x.ContactID FROM ClientContacts x
WHERE x.ClientID IN (SELECT ClientID FROM deleted)
AND NOT EXISTS (
SELECT * FROM ClientContacts y
WHERE x.ContactID=y.ContactID
AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
)
)
DELETE Clients
WHERE ClientID IN (SELECT ClientID FROM deleted)
END
Razvan
|||On Thu, 16 Jun 2005 16:19:05 -0700, Peter Hyssett wrote:

>Hi.
>My system includes Clients and Contacts in a many-to-many relationship
>handled in the usual way using a link table with a primary key of Client key
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present with
>a 'Main Contact' column in the Client table, which for referential integrity
>has a foreign key constraint against the link table. These circular FK
>constraints are a nuisance when it comes to deleting a client. I suppose I
>could use a trigger to enforce referential integrity, but I don't want to -
>the Client table already has a rather complicated trigger.
>Can anyone think of a better way?
>Thanks.
Hi Peter,
I'd consider something like this (stealing lots from Razvan Socol's
post)
CREATE TABLE Contacts (
ContactID int NOT NULL PRIMARY KEY,
FirstName varchar(30) NOT NULL,
LastName varchar(20) NOT NULL,
--other columns--
UNIQUE (FirstName, LastName)
)
CREATE TABLE Clients (
ClientID int NOT NULL PRIMARY KEY,
ClientName varchar(50) NOT NULL UNIQUE,
--other columns--
)
CREATE TABLE ClientContacts (
ClientID int NOT NULL REFERENCES Clients ON DELETE CASCADE,
ContactID int NOT NULL REFERENCES Contacts ON DELETE CASCADE,
Priority smallint NOT NULL CHECK Priority > 0,
PRIMARY KEY (ClientID, ContactID),
UNIQUE (ClientID, Priority)
)
CREATE VIEW PrimaryContact
AS
SELECT cc.ClientID, cc.ContactID
FROM (SELECT ClientID, MIN(Priority) AS MinPrio
FROM ClientContacts
GROUP BY ClientID) AS d
INNER JOIN ClientContacts AS cc
ON cc.ClientID = d.ClientID
AND cc.Priority = d.MinPrio
go
Best, Hugo
(Remove _NO_ and _SPAM_ to get my e-mail address)
|||Peter Hyssett wrote:
> Thanks. I'm afraid the FK is against the link table because the main contact
> must be a contact already linked to the client,
Ah, that makes sense
> which an FK against the
> Contact table would not enforce. Nowadays I do set the Main Contact column to
> NULL before deleting (the link table rows being deleted first), but I would
> prefer not to.
Why would you prefer not to?
Cascade delete _might_ set it to null but Im not sure.
If you wished to keep the MainContactId, then it would be a referential
break as the actual contact would have been deleted.
You could do this by removing the FK constraint though.
JB
|||Thanks, Razvan. The ON DELETE CASCADE construct is what I wanted - I just
hadn't come across it before.
Cheers,
Peter.
Peter Hyssett
"Razvan Socol" wrote:

> Hello, Peter
> As I understand this, your DDL is (or should be) something like this:
> CREATE TABLE Contacts (
> ContactID int PRIMARY KEY,
> FirstName varchar(30) NOT NULL,
> LastName varchar(20) NOT NULL,
> --other columns...
> UNIQUE (FirstName, LastName)
> )
> CREATE TABLE Clients (
> ClientID int PRIMARY KEY,
> ClientName varchar(50) NOT NULL UNIQUE,
> --other columns...
> MainContactID int NULL
> )
> CREATE TABLE ClientContacts (
> ClientID int REFERENCES Clients ON DELETE CASCADE,
> ContactID int REFERENCES Contacts ON DELETE CASCADE,
> PRIMARY KEY (ClientID, ContactID)
> )
> ALTER TABLE Clients ADD CONSTRAINT TheFK
> FOREIGN KEY (ClientID, MainContactID)
> REFERENCES ClientContacts (ClientID, ContactID)
>
> Assuming the following sample data:
> SET NOCOUNT ON
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (100, 'Big Company, Inc.')
> INSERT INTO Contacts VALUES (1, 'John', 'Smith')
> INSERT INTO Contacts VALUES (2, 'Mary', 'Smith')
> INSERT INTO ClientContacts VALUES (100, 1)
> INSERT INTO ClientContacts VALUES (100, 2)
> UPDATE Clients SET MainContactID=1 WHERE ClientID=100
>
> INSERT INTO Clients (ClientID, ClientName)
> VALUES (200, 'Another Company, Ltd.')
> INSERT INTO Contacts VALUES (3, 'John', 'Doe')
> INSERT INTO Contacts VALUES (4, 'Jane', 'Doe')
> INSERT INTO ClientContacts VALUES (200, 2)
> INSERT INTO ClientContacts VALUES (200, 3)
> INSERT INTO ClientContacts VALUES (200, 4)
> UPDATE Clients SET MainContactID=3 WHERE ClientID=200
> SET NOCOUNT OFF
>
> Let's suppose you want to delete the client with the ClientID=100.
> Using a simple "DELETE Clients WHERE ClientID=100" works (without any
> error): the client is deleted and it's links with the contacts, too
> (but the contacts themselves remain). If you want to also delete the
> contacts (the ones that are not linked with any other client), you can
> use the following trigger:
> CREATE TRIGGER Clients_DeleteContacts ON Clients
> INSTEAD OF DELETE
> AS
> IF @.@.ROWCOUNT>0 BEGIN
> SET NOCOUNT ON
> UPDATE Clients SET MainContactID=NULL
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> DELETE Contacts WHERE ContactID IN (
> SELECT x.ContactID FROM ClientContacts x
> WHERE x.ClientID IN (SELECT ClientID FROM deleted)
> AND NOT EXISTS (
> SELECT * FROM ClientContacts y
> WHERE x.ContactID=y.ContactID
> AND y.ClientID NOT IN (SELECT ClientID FROM deleted)
> )
> )
> DELETE Clients
> WHERE ClientID IN (SELECT ClientID FROM deleted)
> END
> Razvan
>