2 FK's to the same table; can't cascade delete

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

2 FK's to the same table; can't cascade delete

Author

Topic

aiken
Aged Yak Warrior

525 Posts

Posted - 2002-02-23 : 13:27:18

Here's a simplified version of my schema:


CREATE TABLE users (
  i INT IDENTITY,
  name VARCHAR(50) NOT NULL
  )

CREATE TABLE user_relationships (
  i_users_source INT PRIMARY KEY,
  i_users_target INT PRIMARY KEY,
  relationship_type TINYINT NOT NULL
  )

As you can see, a relationship is defined by the two users it refers to. I'd like to ensure that if a user is deleted, any relationships involving them are deleted as well.

I've found that I can enable cascade delete for either the i_users_source relationship, or the i_users_target relationship, but not both ("potential for circular reference", or something like that).

I've taken to using an instead of trigger on users to "DELETE FROM user_relationships WHERE i_users_source IN (SELECT i FROM DELETED)", and using the normal FK cascade delete for i_users_target.

But it bugs me, not being able to do it the real way. I don't see any potential for a loop using cascade delete on both FK's. Am I missing something?

Thanks
-b

nizmaylo
Constraint Violating Yak Guru

258 Posts

Posted - 2002-02-23 : 13:51:43

Could you please publish a real schema (without real names used in you DB).

So far, the schema you published will produce the following error:
Cannot add multiple PRIMARY KEY constraints to table 'user_relationships'.

I guessed your schema, but you need to confirm it before we proceed:
CREATE TABLE users (
i INT IDENTITY(1,1) PRIMARY KEY,
name VARCHAR(50) NOT NULL)

CREATE TABLE user_relationships (
i_users_source INT NOT NULL,
i_users_target INT NOT NULL,
relationship_type TINYINT NOT NULL,
CONSTRAINT PK_user_relationships PRIMARY KEY (i_users_source, i_users_target),
CONSTRAINT FK_user_relationships FOREIGN KEY (i_users_source, i_users_target)
REFERENCES users (i) )

helena

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-02-23 : 13:52:05

First, your relationship table is incorrectly defined, you're trying to create 2 primary keys (not possible) on 2 columns, instead of a primary key consisting of 2 colums:

CREATE TABLE user_relationships
(i_users_source INT NOT NULL, i_users_target INT NOT NULL, relationship_type TINYINT NOT NULL
CONSTRAINT PrimaryKeyRelationships PRIMARY KEY (i_users_source, i_users_target)

Secondly, I'm pretty certain you can't enforce that kind of primary key and foreign key due to the circularity issues. For instace, there's nothing in your table definition to prevent the source and target columns from having the same ID. In that instance, which of the foregin keys should be enforced first if an id is deleted from the parent table? It's not just a question of the cascade action; the foreign key must first be enforceable.

There's nothing wrong with using a trigger to perform this action. Either redesign the tables or keep the INSTEAD OF trigger...if it works, then it's the right way to do it.

SNIPED BY 20 SECONDS!

Edited by - robvolk on 02/23/2002 13:52:59

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources