i have a Problem. I created a table in MySQL with a Primary key and two foreign keys. The two foreign keys C and R have to be unique as pair. For the Moment this works.
But then i decided adding a 3rd key CR_New because of a new System feature. Now a combination of C_ID, R_ID and CR_New has to be unique. But it doesn´t work, even with the Solutions i already found in the Internet. I get error 1553 when dropping the foreign keys. A combination of the keys C_ID and R_ID is allowed multiple times if CR_New is different.
CREATE TABLE CR(
CR_ID INT UNSIGNED NOT NULL AUTO_INCREMENT,
CR_Grade TINYINT NOT NULL DEFAULT 0,
C_ID INT UNSIGNED NOT NULL,
R_ID INT UNSIGNED NOT NULL,
PRIMARY KEY(CR_ID),
FOREIGN KEY(C_ID) REFERENCES C(C_ID),
FOREIGN KEY(R_ID) REFERENCES R(R_ID),
CONSTRAINT UNIQUE(R_ID, C_ID));
ALTER TABLE CR ADD COLUMN CR_New INT;
UPDATE CR SET CR_New = 42;
ALTER TABLE CR ADD CONSTRAINT UNIQUE (C_ID, R_ID, CR_New);
ALTER TABLE CR DROP FOREIGN KEY C_ID;
ALTER TABLE CR DROP FOREIGN KEY R_ID;
ALTER TABLE CR DROP INDEX C_ID;
ALTER TABLE CR DROP INDEX R_ID;
If you want to know which use i want to have from it: Just imagine C is a Student, R a subject and CR_New the year, and CR_Grade the grade of the Student. Now it is just possible to save the grade for a subject, but i want to extend it for saving the grade for a subject for each year.
BTW thats not the original use but works the same way.
Now I solved the Problem by creating new tables. After filling them with my data I renamed them and now it works. Should work fine for a test-database :-)