0

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.

  • You need drop old unique constraint http://stackoverflow.com/questions/3487691/dropping-unique-constraint-from-mysql-table – Mike Sep 02 '16 at 11:54
  • how do i find out the Name of the old unique constraint? I know there id SHOW INDEX FROM but how should this be named? –  Sep 02 '16 at 12:41
  • http://stackoverflow.com/questions/3024981/list-of-constraints-from-mysql-database – Mike Sep 02 '16 at 12:44
  • is it possible that it is just names like the first column from the constraint? –  Sep 02 '16 at 12:51
  • just checked, "show index" shows unique constrints on table – Mike Sep 02 '16 at 12:59
  • DROP Constraint_Name FROM CR still gives me error 1553 –  Sep 02 '16 at 13:01
  • try to use `alter table TableName drop index IndexName` – Mike Sep 02 '16 at 13:06
  • thats what i tried atthe first time to remove the foreign keys but it also gives me error 1553 :-( it always says it it used by foreign key constraints, i already tried to drop them because in another question i read that the foreign keys have to be dropped first –  Sep 02 '16 at 13:12
  • this keys in other tables referencing to this table. or you delete the wrong index – Mike Sep 02 '16 at 13:46
  • Start over. Use _real_ column names. Get rid of the `AUTO_INCREMENT` and replace the `PRIMARY KEY` with whatever combination of columns is `UNIQUE`. Don't have any `FOREIGN KEYs`. Then it may be easier to discuss what `FOREIGN KEYs` can/should be added. – Rick James Sep 02 '16 at 22:43

1 Answers1

0

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 :-)