Re: Bidirectional Binary Self-Joins
Date: 29 Mar 2007 19:25:44 -0700
Message-ID: <1175221544.862289.255110_at_l77g2000hsb.googlegroups.com>
If you really want an answer, then really posting DDL instead of some personal pseudo code will help.
CREATE TABLE People
(person_id INTEGER NOT NULL PRIMARY KEY,
person_name VARCHAR(50) NOT NULL);
CREATE TABLE KnowsSomeone -- better name?
(first_person_id INTEGER NOT NULL
REFERENCES People (person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
second_person_id INTEGER NOT NULL,
REFERENCES People (person_id)
ON DELETE CASCADE
ON UPDATE CASCADE,
CHECK (first_person_id <> second_person_id), -- assumption
PRIMARY KEY (first_person_id, second_person_id));
NOw the important part:
CREATE VIEW Friendships (first_person_id, second_person_id)
AS
SELECT first_person_id, second_person_id
FROM KnowsSomeone
UNION ALL
SELECT second_person_id, first_person_id
FROM KnowsSomeone;
Why did you have that unnatural, non-relartional "friendship_id" in your design? I hope you know better than to do use auto-numbering! You even confused rows with records -- that is awful! Tables are not always materialized -- that is why we have views in SQL.
Now write INSTEAD OF TRIGGERs on Friendships.
Some weaker SQL will have problems with the REFERENCES clauses becuase they cannot use the CHECK() constraint. Received on Fri Mar 30 2007 - 04:25:44 CEST