Hi,

I have three tables child, room & toys (crude example)

- A room has many toys
- A child has one room
- A child has one toy.

I want to ensure that a child can only have a foreign key value for a
toy that is assigned to the foreign key value of room they select.

I was thinking of using a trigger to do this.  What do you think?
Would I just need to use the if exists function?

DJW.

- A room has many toys
- A child has one room
- A child has one toy.



CREATE TABLE Toys
(toy_id INTEGER NOT NULL PRIMARY KEY,
toy_name CHAR(13) NOT NULL);

CREATE TABLE Kids
(kid_id INTEGER NOT NULL PRIMARY KEY,
kid_name CHAR(13) NOT NULL);

CREATE TABLE Rooms
(room_nbr INTEGER NOT NULL PRIMARY KEY);

You lock the kid in a room with only one toy??!  I am calling child
protective services!  After I set up this table to keep each toy in its
own room/toybox.

CREATE TABLE ToyBoxes
(room_nbr INTEGER NOT NULL UNIQUE
    REFERENCES Roooms(room_nbr)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
toy_id INTEGER NOT NULL UNIQUE
    REFERENCES Toys(toy_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE
PRIMARY KEY (room_nbr, toy_id) -- for the refs
);

Now attach these deprived kids with a toy box:

CREATE TABLE AbusiveFamily
(kid_id INTEGER NOT NULL PRIMARY KEY
    REFERENCES Kids(kid_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE,
room_nbr INTEGER NOT NULL,
toy_id INTEGER NOT NULL
FOREIGN KEY (room_nbr, toy_id)
REFERENCES Toyboxes (room_nbr, toy_id)
    ON UPDATE CASCADE
    ON DELETE CASCADE);

Thanks for the rely, but it wasn't what I was looking for!  I don't
think my example was particularly good.  Let me explain the real
requirement...

- A Student has one Employer
- An Employer has many Supervisors
- A Supervisor can supervise many Students (that just so happen to work
for the same Employer!)

When inserting data into the student table I want to ensure a user
can't select a supervisor that doesn't work for the Employer they have
selected!

I thought of possibly adding a trigger on the Student to automatically
check if the Supervisor works for the selected supervisor.  Is this the
correct use for a trigger?

Daniel

You don't need this.  Once you have assigned a supervisor, you get the
correct employer - for free.  You have a model problem that you are
attempting to overcome.  BTW- where is the table that reflects the
supervisor / student relationship?  Wait - let me guess.  This is also
stored in the student table?

In general, a trigger enforces business rules.  Frequently, triggers also
"overcome" model problems.  Assuming you want to continue with your current
model, this would be the only way to enforce your particular rules and
prevent the assignment of an employer that is different from the
supervisor's employer.  With the correct model, you can rely on DRI alone to
do all the work (as Joe indicated).

Dynamic Stored Procedure
Dynamic WHERE & Dates
with(updlock) - How many times on same table in single update?
Is there alternative to T-SQL USE <dbname> where <dbname> is varia
help with joining on an index