Re: Cascading update on primary key

From: Joe Celko (joe.celko_at_northface.edu)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 13:22:49 -0800

Please post DDL, so that people do not have to guess what the keys,
constraints, Declarative Referential Integrity, datatypes, etc. in your
schema are. Here is my guess from your narrative:

>> There are 2 (secondary) tables [the kids] whose primary keys depend
on the first table's primary key, and a third table [Barbies] whose
primary key depends on one of the 2 secondary tables [Daughter]. <<

CREATE TABLE Mothers
(mom CHAR(5) NOT NULL PRIMARY KEY);

CREATE TABLE Sons
(mom CHAR(5) NOT NULL
     REFERENCES Mothers(mom)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
 son CHAR(5) NOT NULL);

CREATE TABLE Daughters
(mom CHAR(5) NOT NULL
     REFERENCES Mothers(mom)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
 sis CHAR(5) NOT NULL UNIQUE);

CREATE TABLE Barbies
(sis REFERENCES Daughters(sis)
     ON DELETE CASCADE
     ON UPDATE CASCADE,
 doll CHAR(5) NOT NULL);

How close was my guess?

--CELKO--

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Relevant Pages

  • Re: Design for Category Feature
    ... surname VARCHAR NOT NULL, ... REFERENCES ContactCategories ... ON UPDATE CASCADE ... PRIMARY KEY ); ...
    (comp.databases)
  • Re: object model to table design mapping problem
    ... the INCITS H2 Database Standards Committee(nee ANSI X3H2 ... NOT NULL PRIMARY KEY, ... REFERENCES Courses, ... ON UPDATE CASCADE ...
    (microsoft.public.sqlserver.programming)
  • Re: Selecting several types
    ... Please post DDL, so that people do not have to guess what the keys, ... NOT NULL PRIMARY KEY, --let's use GTIN ... ON UPDATE CASCADE, ... REFERENCES ItemTypes ...
    (comp.databases.ms-sqlserver)
  • Re: String Search
    ... Sample data is also a good idea, ... (proj_nbr INTEGER NOT NULL PRIMARY KEY, ... ON UPDATE CASCADE ... REFERENCES Projects ...
    (microsoft.public.sqlserver.programming)
  • Re: Deriving unique rows from historical data
    ... ON UPDATE CASCADE, ... REFERENCES Locations ... start_time DATETIME NOT NULL, ... Google how to code for this schema. ...
    (comp.databases.ms-sqlserver)
Loading