Hello, Maybe a solution to not allow a vacancy to be added without a supervisor being assigned: 1. Create an atribute of the identification in the supervision table as Primary Key (e.g. id_supervision). 2. Create column id_supervision in vacancy table as not null. 3. Create index in vacancy table to column id_supervision. 4. Create foreign key referencing the column id_supervision in vacancy table and supervision table.
I hope that helps. Regards, Renato Cramer. -----Mensagem original----- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviada em: quarta-feira, 2 de junho de 2004 10:02 Para: eifion herbert (IAH-C) Cc: [EMAIL PROTECTED] Assunto: RI enforcement and m2m relationships. 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]