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

Child/ Parent relationship within table

Hi everyone, I have a categories table which has the following main
attributes
CategoryName, CategoryID <- Identity , ParentCategoryID
What I need help doing is constructing a Procedure/ SQL query where I can
show the expanded relationships for each record in the table.
e.g. If I have three records in the table (Following the attributes
described above)
ParentCategory, 1, 0
ChildCategory, 2,1
SubChildCategory, 3,2
I want to be able to dynamically return the following information when I
execute the query (Each row looks at the parent id and concatenates itself
to its parents CategoryName).
ParentCategory
ParentCategory/ChildCategory
ParentCategory/ChildCategory/SubChildCategory
Thanks in advance
MarlkEverything you are doing is wrong. Let's get back to the basics of an
RDBMS. Rows are not records; fields are not columns; tables are not
files; there is no sequential access or ordering in an RDBMS, so
"first", "next" and "last" are totally meaningless.
Stop using IDENTITY and learn what a relational key is.
Get a copy of TREES & HIERARCHIES IN SQL You are trying to write
(uughh!) procedural code to build a traversal.that will create a path.
This is not the best way; google a "nested sets model" instead.|||There are many, many resources for describing ways to efficiently model
hierarchies and trees in SQL.
Google for the following topics: Nested Sets, Nested Intervals, Adjacency
List, Materialized Path
Here is a good starting point for finding information about this topic:
http://troels.arvin.dk/db/rdbms/links/#hierarchical
As a first step, you might consider creating a new table to manage the
relationship between categories - right now, you are modeling both the
category and the relationship between categories in the same table.
Normalizing the design can give you added flexibility depending on your
requirements.
What you have here is basically an Adjacency List model. This model has
excellent characteristics with regard to modifying the layout of the
hierarchy; you simply change the ParentCategoryID of a node to a different
value, and you instantly "move" that node and all referencing nodes to a
different location in the hierarchy. However, it does not work very well for
retrieving the structure, as you are seeing. SQL Server Books Online has a
section titled "Expanding Hierarchies" that describes an iterative process
of querying the hierarchy that involves using a temporary table as a stack,
but SQL is really optimized for set-based operations. You will probably find
something at the link above that better meets your needs.
"Mark" <dont@.spam.me> wrote in message
news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
> Hi everyone, I have a categories table which has the following main
> attributes
> CategoryName, CategoryID <- Identity , ParentCategoryID
> What I need help doing is constructing a Procedure/ SQL query where I can
> show the expanded relationships for each record in the table.
> e.g. If I have three records in the table (Following the attributes
> described above)
> ParentCategory, 1, 0
> ChildCategory, 2,1
> SubChildCategory, 3,2
> I want to be able to dynamically return the following information when I
> execute the query (Each row looks at the parent id and concatenates itself
> to its parents CategoryName).
> ParentCategory
> ParentCategory/ChildCategory
> ParentCategory/ChildCategory/SubChildCategory
> Thanks in advance
> Marlk
>
>
>
>|||Whoa! Cool, thanks for the links
Cheers
Mark
"Jeremy Williams" <jeremydwill@.netscape.net> wrote in message
news:ekyuNZLuFHA.2072@.TK2MSFTNGP14.phx.gbl...
> There are many, many resources for describing ways to efficiently model
> hierarchies and trees in SQL.
> Google for the following topics: Nested Sets, Nested Intervals, Adjacency
> List, Materialized Path
> Here is a good starting point for finding information about this topic:
> http://troels.arvin.dk/db/rdbms/links/#hierarchical
> As a first step, you might consider creating a new table to manage the
> relationship between categories - right now, you are modeling both the
> category and the relationship between categories in the same table.
> Normalizing the design can give you added flexibility depending on your
> requirements.
> What you have here is basically an Adjacency List model. This model has
> excellent characteristics with regard to modifying the layout of the
> hierarchy; you simply change the ParentCategoryID of a node to a different
> value, and you instantly "move" that node and all referencing nodes to a
> different location in the hierarchy. However, it does not work very well
for
> retrieving the structure, as you are seeing. SQL Server Books Online has a
> section titled "Expanding Hierarchies" that describes an iterative process
> of querying the hierarchy that involves using a temporary table as a
stack,
> but SQL is really optimized for set-based operations. You will probably
find
> something at the link above that better meets your needs.
> "Mark" <dont@.spam.me> wrote in message
> news:%233O9T%23KuFHA.1560@.TK2MSFTNGP09.phx.gbl...
can
itself
>

Child package ConnectionManager visibility

Hopefully a simple question about parent-child package relationship. For this example, let's say I have a simple setup - one parent package: parent.dtsx, and one child package: child.dtsx. The parent package calls the child package via the ExecutePackage Task.

If I add an OleDB ConnectionManager to the parent package called MySqlConnectionManager, should I be able to reference this connection via a script task (or custom component) from my child package? I realize that I will have a problem doing this at design time, but I thought I could get around it with the script task or custom component. That said, when I look in the Connections collection at run-time from within my child package, I do not see the parent package's MySqlConnectionManager. Am I missing something, or is this the way it was intended to work?

Thanks,

David

David,

I suspect you cannot do this. Connection managers can only be used in the package in which they reside - even if you're using a script task.

-Jamie

|||

Jamie,

Thanks for the response. I must say it is somewhat disappointing, though I think I have a work around for my situation. That said, I would still be interested in hearing a rationale for why this is the case. It seems to me like it breaks the container hierarchy paradigm.

David

|||

Well I can see why you think this but remember that connection managers don't follow container scope like variables do so the same rules don't apply.

Having said that, there were plans to scope conenction managers to the container hierarchy but it couldn't be done in time (or something). Reading between the lines its something they (well...kirk Haselden) wanted to do but it was down the priority list.

-Jamie

|||

Thanks again, Jamie. Hopefully this will be implemented at some point in the future.

Related, I found the opposite to be true when dealing with log providers. Interestingly, the connections collection of the parent package DOES appear to be available to child package log providers (I have built a custom log provider in which this appears to be true). It strikes me as bizzare that the functionality I want is there for log providers, but not for the package tasks. That may be due, however, to gaps in my understanding of parent-child package relationships.