Re: expressing one-or-more constraint structurally

"--CELKO--" <jcelko212_at_earthlink.net> wrote in message news:18c7b3c2.0406262014.6895664d_at_posting.google.com...

> >> .. a cardinality constraint on one item vs. another, being able to
> do it structurally is the best approach. <<
>
> Here is an old "cut & paste" of mine that might help:

Thanks! That was a very interesting post. I read it a few times, and I'm glad I did.

(Minor note: in some of the tables (Pairs, Orgys, Playboys,
Playgirs, Couples) the foreign keys don't have the same type as the keys they reference.)

Maybe I'm missing something, though. I don't quite see how to use unique keys to create a one-to-one-or-more relationship.

Let's say I want it that a teacher has one or more classes.

Let's say I have a simplified version of your Schedule table.

CREATE TABLE Schedule
(teacher VARCHAR(15) NOT NULL,

 class CHAR(15) NOT NULL,
 PRIMARY KEY (teacher, class));

Okay, so now I can do this:

('Mr. Celko', 'Database 101')
('Mr. Celko', 'Database 102')

So I have one teacher, one or more classes, and you can't be in the table unless you have an associated class, because 'class' is NOT NULL.

But what if there are additional attributes associated with each teacher?

CREATE TABLE Schedule
(teacher VARCHAR(15) NOT NULL,

 class CHAR(15) NOT NULL,
 handle VARCHAR(15) NOT NULL,
 PRIMARY KEY (teacher, class));

('Mr. Celko', 'Database 101', '--CELKO--')
('Mr. Celko', 'Database 102', '--CELKO--')

Whoops! Now my table is denormalized.
Bring forth the update anomalies!

So I move (teacher, handle) to another table entirely to normalize, but then I lose the at-least-one property that Schedule has. (BTW, can you tell I'm trying to pay attention to your "use DDL please" plea?)

CREATE TABLE Teachers
(teacher VARCHAR(15) NOT NULL,

 handle VARCHAR(15) NOT NULL,
 PRIMARY KEY (teacher));

CREATE TABLE Schedule
(teacher VARCHAR(15) NOT NULL REFERENCES Teachers(teacher),
 class CHAR(15) NOT NULL,
 PRIMARY KEY (teacher, class));

I can still have this:

Teachers:
('Mr. Celko', '--CELKO--')

Schedule:
('Mr. Celko', 'Database 101')
('Mr. Celko', 'Database 102')

So I've got my more-than-one property, but I can say DELETE FROM Schedule and nothing's to stop me. So then I'd have a teacher with zero classes. But I wanted to find some way to constrain it so that every teacher always had at least one, and possibly many class.

I've played around with it a few different ways, but I'm still not getting it.

Marshall Received on Sun Jun 27 2004 - 08:02:26 CEST