Tuesday, March 20, 2012
Circular FK Constraints
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 wit
h
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 HyssettPeter 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 k
ey
> plus Contact key and foreign key constraints against Client and Contact
> tables. Each Client may have a Main Contact, which is handled at present w
ith
> a 'Main Contact' column in the Client table, which for referential integri
ty
> 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 t
o
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 ke
y
>plus Contact key and foreign key constraints against Client and Contact
>tables. Each Client may have a Main Contact, which is handled at present wi
th
>a 'Main Contact' column in the Client table, which for referential integrit
y
>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 conta
ct
> 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 woul
d
> 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
>
Circular FK Constraints
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 HyssettPeter 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:
> > 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
>|||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
>
Circular FK Constraints
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
>
Circular dependencies from constraints
Hey!
I am creating a kind of file browser for an application of mine. The principle is quite straight forward. It consists of folders and files. Each folder can contain other folders and files and so on. The twitch however is that i need a special root entity called site. The site is very much alike a folder but has some other properties. The site can contain folders and files.
To achieve this ive created te following tables (truncated for clearity):
###################
# Sites #
###################
# ID [Int] #
# ... #
###################
###################
# Folders #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################
###################
# Files #
###################
# ID [Int] #
# SiteID [Int] #
# FolderID [Int] #
# ... #
###################
Both the folder table and the files table have a check constraint that ensures that either SiteID or FolderID is NULL. They WILL be part of EITHER a folder or a site. Not both!
Then i set up the foreign constraints as follows:
Folders.FolderD -> Folders.ID
Folders.SiteID -> Sites.ID
Files.FolderID -> Folders.ID
Files.SiteID -> Sites.ID
All constraints have cascade on delete and therefore the last of them cannot be created as it would be circular. (Wich it wont in this case, but theoreticaly its possible)
Iknow WHY this is rendering an error. But how can i work around it? Or would you suggest another design of the tables?
Hi Supermajs,
Firslty, I would remove the constraints.
Secondly, remove the SiteID and FolderID fields from the Folders and Files tables.
Thirdly, add the fields ParentID and ParentTypeID to the Folders and Files tables.
Then add a new table called tblParentType with the following records: -
ParentTypeID ParentType
1 Site
2 Folder
Then add a constraint between ParentType.ParentTypeID and Folders.ParentTypeID and between ParentTypeID.ParentTypeID and Files.ParentTypeID.
Now a record within Folders will look like this: -
ID ParentID ParentTypeID
1 3 1 this folder is a folder under site id 3
2 14 2 this folder is a folder under folder id 14
The same applies for the Files table.
By doing it this way, you can easily add new levels without needing extra fields that always contain NULL.
For example, you could add a new layer above site called server. The ParentType table would be as follows: -
ParentTypeID ParentType
1 Server
2 Site
3 Folder
|||
.In relational modeling it is files and association, I think you have designed flat files that could be a problem because DRI(declarative referential integrity) comes with restrictions. If you have 50 files that are associated they belong together that is how you can start with 100 files and end up with 5 tables. Try the Normalization tutorial and free data models to clean up your design and post again so I can help you with the DRI Cascade operation because it comes with fixed requirement of if A references B then B must exist. Hope this helps
http://www.utexas.edu/its/windows/database/datamodeling/rm/rm7.html
http://www.databaseanswers.org/data_models/
Wednesday, March 7, 2012
checks or constraints?
and I want to put a constraint that availability_date should
always be earlier than offer date and if values are inserted into
the table it should return DBMS_server_output:
"offer_date cannot be earlier than availability_date"
Name Null? Type
PROPERTY_ID VARCHAR2(10)
TYPE VARCHAR2(10)
ASKING_PRICE NUMBER(10,2)
SELLING_PRICE NUMBER(10,2)
OFFER_DATE DATE
AVAILABILITY_DATE DATEYou will most likely have to implement using a TRIGGER since you have a specific message you want to display. However, since this is a non-dbms specific area, and not all DBMSs will allow you to implement a trigger, I will not recommend any specific trigger syntax.|||An alternative could be to store the difference of the two instead of the availability_date (possibly with a view on top of this, returning the two dates).
Then you could use a simple check constraint ( >= 0) on that difference.|||I'm 99.9% certain you are using Oracle, right?
You can create a check constraint like this:
alter table x add constraint dates_chk check (offer_date >= availability_date);
However, that will not give you the specific error message you mentioned, it will give you:
ORA-02290: check constraint (MYSCHEMA.DATES_CHK) violated
It is easy to trap that error message in an application, see what constraint was violated, and present a better message. I would not advocate using a trigger instead merely to allow a bespoke message. (Aside: it would be nice if Oracle allowed you to define bespoke error messages for each constraint, wouldn't it!)
I note you said this: "it should return DBMS_server_output [the message]". I presume you mean DBMS_OUTPUT.PUT_LINE? If so, that is wrong: never use DBMS_OUTPUT to handle error messages, it is only suitable for simple debugging etc. If you were writing a trigger you should call raise_application_error like this:
raise_application_error(-20001,'offer_date cannot be earlier than availability_date');
Sunday, February 19, 2012
Checking if CHECK Constraints exist
Hi,
How do I check if a check constraint on a table already exists or not?
Thank you.
see the Books Online for more information on INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE|||Thank you...checking for unique constraints during a batch update
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.
the problem is that some of the table fields have to be unique
now imagine the next situation.
user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch
user B writes '1000' in the unique field
user B saves the recordset with updatebatch
now there will be two records with the field '1000' !
how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset
thanks !It is not a good idea to ask users input data for unique field in multi-user application. Use identity, triggers, etc.|||There is another solution, I just wanted to share with you a control featured on download.com that I have been using that has made my life easier, and it solves exactly the problem you are describing. When your user presses the update button on this control it automatically checks for conflicts and lets the user decide what to do. You can try it for free here:
http://download.com.com/3000-2404-10257755.html?tag=lst-0-1
Basically, after selecting the CursorType, CursorLocation, LockType and Disconnected status of your Recordset (there are defaults). I bind my controls and that is it All the complexities of ADO are hidden from the user which is you.
Then I can Navigate, Add New, Delete, Refresh, Search and Save Changes to my recordsets without any other code. That is really it, no other code is needed.
This works in any environment VB, Access, VC++ etc...
Let me know what you think, and if it works out for you.
Originally posted by alexandros
hello .
i have a grid for a table that gets updated with recordset.updatebatch
for a multi-user application.
the problem is that some of the table fields have to be unique
now imagine the next situation.
user A opens the form
user B opens the form
user A writes '1000' in the unique field
user A saves the recordset with updatebatch
user B writes '1000' in the unique field
user B saves the recordset with updatebatch
now there will be two records with the field '1000' !
how can i avoid this ?
i cannot check for unique during the update event of the grid
because it should check during the time it is saving and not
when it is just entering data without having updated the recordset
thanks !
Thursday, February 16, 2012
checking dates with triggers or constraints
I have a table that contains various data with a start and end date. When a user enters the information I need to check that the end date is older than the start date.
What is the easiest way to do this - using a trigger?
And is the code below correct ?
CREATE TRIGGER checkdate
ON Prodn_Problem
FOR INSERT,UPDATE
AS
DECLARE @.start_date datetime
SELECT @.start_date = (SELECT startdate FROM inserted)
DECLARE @.end_date datetime
SELECT @.end_date = (SELECT enddate FROM inserted)
BEGIN
IF(@.end_date < @.start_date)
ROLLBACK
END
Thanks.By using a trigger, you check the data when it's already inserted or updated in the table. When you use a check constraint you can verify before the information is entered. Another method is to create a stored procedure which does the checking an let you users enter information through this stored procedure.