I don't see how you can possibly hope to satisfy two mutually dependent
constraints at the exact same time. It's a chicken-and-egg scenario. If you
can't create your vacancy record first (to get its PK value) you won't be
able to create the new record in your supervision table.
Most people solve this dependency dilemma with either application-layer
processing or by creating a stored procedure (new to MySQL 5.x). Many
people also wrap the entire process in a transaction (supported in InnoDB)
so that if you somehow fail to create the new supervision record then you
would be able to rollback the transaction (un-creating the vacancy record),
leaving your database in a consistent state (no vacancy would exist unless
it has a corresponding supervision record).
It's not hard to do what you want, but it requires logic external to the
storage engine itself to enforce that kind of relationship.
Yours,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
"eifion herbert
(IAH-C)" To: <[EMAIL PROTECTED]>
<[EMAIL PROTECTED] cc:
bsrc.ac.uk> Fax to:
Subject: RI enforcement and m2m
relationships.
06/02/2004 06:45
AM
Hi all,
I'm developing a database to store job adverts to appear on a company
website.
Each vacancy has at least one supervisor, but may have more than one,
and a supervisor may be responsible for multiple vacancies.
I've resolved the vacancy<-->supervisor many-to-many relationship in the
normal(?) way of having a table called supervision inbetween them with
two columns, the Ref of the vacancy and the ID of the supervisor. Both
these columns make up the pri key in the supervision table, and are also
foreign keys referencing the pri keys in the vacancy and supervisor
table.
Unfortunately this allows a vacancy to be added to the database without
a supervisor being assigned.
Is there a way in mySQL4 or Perl to enforce this without creating an
insertion anomaly?
Cheers
Eifion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
