> > We have one main table for our users, as well as several related
tables
> > that contain user information. We would like to set up triggers
so
> > that the following occurs:
> >
> > 1. If someone deletes a user from the user table, it deletes all
the
> > occurences of the user's information in all the related tables.
> > 2. If someone tries to delete an entry from a related table, it
won't
> > allow it.
> >
> Can't that be handled with foreign key constraints, ala
>
> CASCADE
>
> Automatically drop objects that depend on the dropped column or
constraint
>
> RESTRICT
> Refuse to drop the column or constraint if there are any dependent
> objects. This is the default behavior.
It would be nice if I could do it with foreign keys. If I use a
foreign key on the related tables and specify 'ON DELETE CASCADE", the
items from the related tables are removed on the deletion of users
(which is correct), but I am still able to delete items from the table
individually. The way I see it, I'm going to need to create columns for
references to the related tables within the user table and then use a
foreign key on those columns in the user table as well. That way, if
you attempt to delete an item from a related table, it sees that there
is still an entry in the users table and doesn't allow for deletion.
Is there a way to use foreign keys in the way that I described above
without having to create reference columns on the user table?