Re: Relation problem
Date: Fri, 26 Jan 2001 19:41:34 GMT
Message-ID: <3a71ad7f.8334584_at_news.compuserve.com>
On 26 Jan 2001 13:03:09 GMT, hidders_at_REMOVE.THIS.win.tue.nl (Jan
Hidders) wrote:
>Sure, but then you are dropping the constraint the the Organization
The Organization table isn't the union of anything. Do you *know*
what a supertype is? Judging by your contribution to this thread, I'd
have to say you don't. It looks like you're just throwing words
>table is the union of the two "subtables". That's fine as long as you
>are clear about what every tuple in every table means.
Maybe these bite-sized pieces of SQL will help.
CREATE TABLE Parties (
PartyID INTEGER NOT NULL PRIMARY KEY,
PartyName CHAR(30) NOT NULL,
PartyType CHAR(3) NOT NULL
);
ALTER TABLE Parties
ADD CONSTRAINT TypeIsValid
CHECK (PartyType IN ('Ind', 'Org'));
INSERT INTO Parties VALUES (1, 'Savealot, Eva', 'Ind'); INSERT INTO Parties VALUES (2, 'Wibble, Inc.', 'Org');
CREATE TABLE Persons (
PersonID INTEGER NOT NULL PRIMARY KEY,
HeightInInches INTEGER NOT NULL
);
ALTER TABLE Persons
ADD CONSTRAINT FKPID
FOREIGN KEY (PersonID) REFERENCES Parties (PartyID);
ALTER TABLE Persons
ADD CONSTRAINT IDRefersToIndividual
CHECK (EXISTS
(SELECT PartyID FROM Parties
WHERE (
(Parties.PartyID = Persons.PersonID) AND
(Parties.PartyType = 'Ind')
)
)
);
INSERT INTO Persons VALUES (1, 72);
CREATE TABLE Orgs (
OrgID INTEGER NOT NULL PRIMARY KEY,
TaxID CHAR(9) NOT NULL
);
ALTER TABLE Orgs
ADD CONSTRAINT FKOID
FOREIGN KEY (OrgID) REFERENCES Parties (PartyID);
ALTER TABLE Orgs
ADD CONSTRAINT OrgIsOrg
CHECK (
EXISTS (
SELECT PartyID FROM Parties
WHERE (
(Parties.PartyID = Orgs.OrgID) AND (Parties.PartyType = 'Org')
)
)
);
INSERT INTO Orgs VALUES (2, '23-4432');
-- Mike Sherrill Information Management SystemsReceived on Fri Jan 26 2001 - 20:41:34 CET