Hi jesse,
You can only set the reference in your constraint:
... ADD FOREIGN KEY (id) references table(id) ...
In this case you will get an inconsistent database. Dont reference the
two tables Campers and Counselers with the ActivitySelections table, but
use two tables in which you put the references:
- CampersActivitySelections
- CounselorsActivitySelections
These tables will have the keys from Campers and Counselors and the
ActivitySelections table. The matter is perhaps that you can have the
same Activity for both Campers and Counselers, than you would have a
problem in the old situation.
As a db schema:
Campers - CampersActivitySelections - ActivitySelections
Counselors - CounselorsActivitySelections - ActivitySelections
In your selection you can use the many-to-many tables to create your
selection-output.
You won't need a Type field in this case and your ActivitySelections
holds the data pure for this perpose.
Hope this small info will help you on your way :-)
Best regards,
Danny
Jesse wrote:
I need to be able to add a foreign key that will allow a constant is
possible.
I have a table named Campers that has a field named ID. I also have
another table called Counselors that has a field named ID as well.
There is a detailed table called ActivitySelections that I use for both
Campers and Counselors. In ActivitySelections, the field PersonID holds
the ID value from either Camper or Counselor, and I've got another field
named Type. Type='C' where we're dealing with a Camper, and it holds 'O'
where we're dealing with a Counselor. I tried the following, but got an
error:
ALTER TABLE activityselections DROP FOREIGN KEY FK_Campers,
ADD CONSTRAINT FK_Campers FOREIGN KEY FK_Campers (PersonID, Type)
REFERENCES campers (ID, 'C')
ON DELETE CASCADE
ON UPDATE CASCADE;
Is what I'm trying to do possible, or do I need to go back to the
drawing board, or do this manually?
Thanks,
Jesse
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
