Re: Foreign Key with constant? - Jesse - com.mysql.lists.mysql

3 messages in com.mysql.lists.mysqlRe: Foreign Key with constant?

From	Sent On	Attachments
Jesse	26 Dec 2005 11:49
Danny Stolle	27 Dec 2005 03:27
Jesse	27 Dec 2005 05:27

Subject:	Re: Foreign Key with constant?
From:	Jesse (jl...@msdlg.com)
Date:	12/27/2005 05:27:38 AM
List:	com.mysql.lists.mysql

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.

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,

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?