Path: newssvr20.news.prodigy.com!newsmst01.news.prodigy.com!prodigy.com!pd7cy2so!shaw.ca!border1.nntp.ash.giganews.com!border2.nntp.sjc.giganews.com!border1.nntp.sjc.giganews.com!nntp.giganews.com!local1.nntp.sjc.giganews.com!nntp.comcast.com!news.comcast.com.POSTED!not-for-mail NNTP-Posting-Date: Sat, 14 Feb 2004 16:00:34 -0600 Subject: Re: Help with cascading deletion in foreign keys Date: Sat, 14 Feb 2004 17:00:33 -0500 From: Dave Sugar Newsgroups: comp.databases.theory Message-ID: <140220041700331547%dsugar100@comcast.net> References: <55ab74ad.0402091445.84a8dc2@posting.google.com> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-transfer-encoding: 8bit User-Agent: Thoth/1.7.1 (Carbon/OS X) Lines: 100 NNTP-Posting-Host: 68.45.48.88 X-Trace: sv3-qfY7Gw3nwMsEwaG/3YdvXJW73348qWKO6Ygv1wvXJm/91Sz9SsUHXQzMJl6Qp45mT712e4X3irO3vMO!IZfzf7Ih3PChCm1rAb5SbHCOsQ0n79gtVdh8UMiZycZZlLnYKJFfPRznermyVCLQ3W8= X-Complaints-To: abuse@comcast.net X-DMCA-Complaints-To: dmca@comcast.net X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.1 Xref: newssvr20.news.prodigy.com comp.databases.theory:23833 Thanks for these suggestions. The triggers containgin deletes make a lot of sense to me and it isn't something that I would have considered on my own. I think it will solve the referential integety items that I was concerned about in the database. Dave Sugar dsugar@dolphinsoft.com In article , --CELKO-- wrote: > CREATE TABLE Students > (stu_id INTEGER NOT NULL PRIMARY KEY); > > CREATE TABLE Classes > (class_id INTEGER NOT NULL PRIMARY KEY); > > CREATE TABLE Registration > (stu_id INTEGER NOT NULL, > REFERENCES Students (stu_id) > ON UPDATE CASCADE > ON DELETE CASCADE, > class_id INTEGER NOT NULL > REFERENCES Classes > ON UPDATE CASCADE > ON DELETE CASCADE, > PRIMARY KEY (stu_id, class_id)); > > How it should function: > > 1) For each student there is a [single] row in the Students table. > Done via PRIMARY KEY on Students. > > 2) For each class there is a [single] row in the Classes table > Done via PRIMARY KEY on Classes. > > 3) As a student is added to a class there is a row added to the > Registration table. > Done via compound PRIMARY KEY on Registration. > > 4) If a student is in multiple Classes there are multiple rows for > that student in the Registration table. > Done via compound PRIMARY KEY on Registration. > > 5) If a student is removed from a class the row is removed from the > Registration table. > Done via compound PRIMARY KEY on Registration. > > 6) When a student is removed from their last class the student row is > removed from the Students table. > > DELETE FROM Student > WHERE NOT EXISTS > (SELECT * > FROM Registration AS R1 > R1.stu_id = Student.stu_id); > > 7) If a class is removed all the rows in Registration are removed from > that class. And all Students who were only in that class are deleted > but Students that are still in other Classes are not deleted. > > DELETE FROM Classes > WHERE NOT EXISTS > (SELECT * > FROM Registration AS R1 > WHERE R1.class_id = Classes.class_id); > > Okay, we could put these DELETEs in triggers or even a CREATE > ASSERTION statement. But try this nightmare: > > > CREATE TABLE Students > (stu_id INTEGER NOT NULL PRIMARY KEY, > stu_id_1 INTEGER NOT NULL UNIQUE > REFERENCES Registration (stu_id) > ON DELETE CASCADE, > CHECK (stu_id = stu_id_1)); > > CREATE TABLE Classes > (class_id INTEGER NOT NULL PRIMARY KEY, > class_id_1 INTEGER NOT NULL UNIQUE > REFERENCES Registration (class_id) > ON DELETE CASCADE, > CHECK (class_id = class_id_1) > ); > > CREATE TABLE Registration > (stu_id INTEGER NOT NULL UNIQUE > REFERENCES Students (stu_id) > ON DELETE CASCADE, > class_id INTEGER NOT NULL UNIQUE > REFERENCES Classes > ON DELETE CASCADE, > PRIMARY KEY (stu_id, class_id)); > > This passes the Mimer validation, but you'd better defer constraints > so you can insert data into the tables.