I thought that might be the only solution to this problem, but didn't know
if there was a way to keep it intact. I will split the tables up.
Thanks,
Jesse
----- Original Message -----
From: "Danny Stolle" <[EMAIL PROTECTED]>
To: "Jesse" <[EMAIL PROTECTED]>; <mysql@lists.mysql.com>
Sent: Tuesday, December 27, 2005 6:27 AM
Subject: Re: Foreign Key with constant?
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]
