Re: unknown symbol in ER diagram

From: onedaywhen (jamiecollins_at_xsmail.com)
Date: 02/22/05 

Date: 22 Feb 2005 06:34:17 -0800

> Tim Ferguson wrote:
> > Ideally, there should be a rule to prevent
> > any Policy existing in more than one subtype table, but AFAIK this
is
> not a
> > feature of any existing RDBMS
>
> Do a google groups search for the exact phrase "CREATE TABLE SUV" for
a
> Joe Celko post on the subject. <Quote> use a compound candidate
> key (vin, vehicle_type) and a constraint in each sub-class table to
> assure that the vehicle_type is locked and agrees with the Vehicles
> table </Quote>.

Thinking about this some more, if you don't like the vehicle_type style
lockdown, I think you could use CHECK constraints to do the same thing.
The following is quick/dirty and off the top of my head (there must be
a more efficient way than NOT IN):

CREATE TABLE Main (
  main_ID INTEGER NOT NULL PRIMARY KEY
)
;
CREATE TABLE Sub1 (
  main_ID INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT fk__sub1 FOREIGN KEY (main_ID)
    REFERENCES Main (main_ID)
)
;
CREATE TABLE Sub2 (
  main_ID INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT fk__sub2 FOREIGN KEY (main_ID)
    REFERENCES Main (main_ID)
)
;
CREATE TABLE Sub3 (
  main_ID INTEGER NOT NULL PRIMARY KEY,
  CONSTRAINT fk__sub3 FOREIGN KEY (main_ID)
    REFERENCES Main (main_ID)
)
;
ALTER TABLE Sub1 ADD CONSTRAINT ch__sub1
  CHECK (
    main_ID NOT IN (SELECT main_ID FROM Sub2)
    AND main_ID NOT IN (SELECT main_ID FROM Sub3)
)
;
ALTER TABLE Sub2 ADD CONSTRAINT ch__sub2
  CHECK (
    main_ID NOT IN (SELECT main_ID FROM Sub1)
    AND main_ID NOT IN (SELECT main_ID FROM Sub3)
)
;
ALTER TABLE Sub3 ADD CONSTRAINT ch__sub3
  CHECK (
    main_ID NOT IN (SELECT main_ID FROM Sub1)
    AND main_ID NOT IN (SELECT main_ID FROM Sub2)
)
;

Jamie. 

--

Relevant Pages

  • SQL Server confused about primary keys. So am I.
    ... We are generating a script to convert a client's database to Sql ... ALTER TABLE dbo.Categories ADD ... CONSTRAINT PK_Categories PRIMARY KEY CLUSTERED ...
    (microsoft.public.sqlserver.server)
  • Re: Alter table help
    ... alter table table_name add constraint constraint_name primary key ... > "Tom Pennington" wrote in message ... >> Okay, I'm trying to modify a tables primary key, actually, I'm trying to ...
    (microsoft.public.sqlserver.programming)
  • Re: Drop Primary Key with SQL/VBA
    ... >> Syntax error in ALTER TABLE statement. ... > Do you want to drop the Primary Key constraint on a field..... ... > CREATE TABLE tblShipping ...
    (microsoft.public.access.queries)
  • Re: Database design question
    ... say we have document and we need to specify permissions to the ... Is there anyway we can have constraint like ... alter table DocumentPermission ... You also need a primary key for DocumentPermission. ...
    (comp.databases.theory)
  • Re: Check Constraints using a subquery - SSrvr 2000
    ... typically refer to the primary key of the referenced table, ... constraint or unique index references are also allowed. ... >> One usually uses a foreign key constraint to enforce a required ...
    (microsoft.public.sqlserver.programming)
Loading