Re: Bidirectional Binary Self-Joins

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 Thu Mar 29 2007 - 21:25:44 CDT