Re: Foreign key pointing to multiple tables

joe.celko_at_northface.edu (--CELKO--) wrote in message news:<a264e7ea.0309221130.64fc919d_at_posting.google.com>...
> Yep, we need a priamry key in each of the job tables to prevent
> duplicates.
>
> CREATE TABLE A_jobs
> (job_ticket INTEGER NOT NULL PRIMARY KEY
> REFERENCES Tickets(job_ticket),
> job_type CHAR(1) DEFAULT 'A' NOT NULL
> CHECK(job_type = 'A'),
> FOREIGN KEY(job_ticket, job_type)
> REFERENCES Tickets (job_ticket, job_type)
> ON DELETE CASCADE
> ON UPDATE CASCADE,
> ...);
>
> But I do need the FOREIGN KEY (job_ticket, job_type) to make that the
> job_ticket bellongs to one and only one job_type.

I agree on that, but I was wondering whether

(job_ticket INTEGER NOT NULL PRIMARY KEY

         REFERENCES Tickets(job_ticket),

is necessary. Couldnt we just do

(job_ticket INTEGER NOT NULL PRIMARY KEY,

Since job_type is fixed by the constraint

CHECK(job_type = 'A'),

the constraint

REFERENCES Tickets (job_ticket, job_type)

should be sufficient. I agree that there probably will not be any overhead by including REFERENCES Tickets(job_ticket), but is it strictly necessary, from a theoretical and/or practical point of view.

Kind regards
/Lennart Received on Tue Sep 23 2003 - 00:57:15 CDT