I have created a table to store phone, fax, pager and email addys.
I have a column for storing a contact_type.
This contact_type can be a PK from any one of 3 tables (customer contact, supplier contact or a customer).
Can I make 3 seperate constraints with contact_type as the FK?
CODE
CONSTRAINT `communications_fk1` FOREIGN KEY (`contact_type`)
REFERENCES `contacts` (`contact_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `communications_fk2` FOREIGN KEY (`contact_type`)
REFERENCES `customers` (`customer_ID`) ON DELETE CASCADE ON UPDATE CASCADE,
CONSTRAINT `communications_fk3` FOREIGN KEY (`contact_type`)
REFERENCES `suppliers` (`supplier_ID`) ON DELETE CASCADE ON UPDATE CASCADE
Will it throw an error on the first constraint if is not in the contacts table? Or will it check all 3 before throwing an error?
Does this even make sense to do? Or should I just store this within each of the 3 tables?
Thanks for your help!
This post has been edited by dedman: 8 Dec, 2006 - 08:16 AM
| 