Can not add new column with foreign key

Question

I have a MySQL table and now I want to add a new column with Primary Key and Foreign Key.

What I've tried so far

How the table look like:

CREATE TABLE IF NOT EXISTS `results` (
  `id` int(20) NOT NULL UNIQUE AUTO_INCREMENT,
  `resultA_id` int(20) NOT NULL,
  `resultB_id` int(20) NOT NULL,
  `resultC_id` int(20) NOT NULL,
  `resultD_id` int(20) NOT NULL,
  `resultE_id` int(20) NOT NULL,
  `resultF_id` int(20) NOT NULL,
  FOREIGN KEY (resultA_id) REFERENCES resultA(id),
  FOREIGN KEY (resultB_id) REFERENCES resultB(id),
  FOREIGN KEY (resultC_id) REFERENCES resultC(id),
  FOREIGN KEY (resultD_id) REFERENCES resultD(id),
  FOREIGN KEY (resultE_id) REFERENCES resultE(id),
  FOREIGN KEY (resultF_id) REFERENCES resultF(id),
  PRIMARY KEY (`resultA_id`, `resultB_id`, `resultC_id`, `resultD_id`, `resultE_id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

How such e.g. a resultA table look like

CREATE TABLE IF NOT EXISTS `resultA` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

Now I want to add a new Column to results with a relation to a new table called table_test. Therefor I've created a new table_test

CREATE TABLE IF NOT EXISTS `table_test` (
  `id` int(20) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL UNIQUE,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=ascii COLLATE=ascii_bin;

Than added a new column to results

ALTER TABLE results ADD COLUMN test_id;

Now I want to add a primary key

ALTER TABLE `results` ADD PRIMARY KEY(`test_id`);

And I got this error

Cannot drop index 'PRIMARY': needed in a foreign key constraint

I've also tried to add a foreign key like this here

ALTER TABLE `results` ADD  CONSTRAINT `results_ibfk_7` FOREIGN KEY (`test_id`) REFERENCES `table_test`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT;

and than I got this error

Cannot add or update a child row: a foreign key constraint fails

I've tried to ignore the foreign key check like this here

SET FOREIGN_KEY_CHECKS=0

But this does not help.

I've also tried to create a new table with the new column and than try to copy the data from the old table to the new one

INSERT INTO results_new SELECT * FROM results;

Than I also got this error here

Cannot add or update a child row: a foreign key constraint fails

What can I do or what I'm doing wrong here?

EDIT What I've done is now. Creating the new table with the new column and foreign key and so on. Than dump the old table to a CSV file. Edit this CSV file and add the new value to every line in the csv file. Import this CSV file into the new table. This works! Thanks all for your help!

The basic design seems to be fairly bad - IMHO. Why in the first place do you have a table with so many keys in the primary key? It looks like the primary key contains most fields except for what I would have thought is the main one which is ID?! — Nigel Ren, Apr 28 '17 at 09:37

score 1 · Accepted Answer · edited May 23 '17 at 12:18

1

I think what you are trying to do is to add the test_id field to the primary key for table results.

As stated here (source question), you cannot do that with SQLite.

If you want to do that with MySQL, you have to drop the exsisting primary key and recreate it (source).

ALTER TABLE MyTable
  DROP PRIMARY KEY,
  ADD PRIMARY KEY (old_col1, old_col2, new_col);

edited May 23 '17 at 12:18

Community

1
1

answered Apr 28 '17 at 09:38

Cynical

9,328
1
15
30

And what can I do to achieve what I want? – Elec Apr 28 '17 at 09:41
If you REALLY want to keep that design, you have to recreate the table with the updated primary key, then copy your data from the old one, then drop the old one. – Cynical Apr 28 '17 at 09:43
For the primary key this worked. Thanks. Now I want to add a foreign key like this here ALTER TABLE `results` ADD CONSTRAINT `results_ibfk_7` FOREIGN KEY (`test_id`) REFERENCES `table_test`(`id`) ON DELETE RESTRICT ON UPDATE RESTRICT; But than I got a foreign key error – Elec Apr 28 '17 at 09:45
And again, you cannot do that with SQLite, as is clearly stated in the first link I gave in my answer. – Cynical Apr 28 '17 at 09:49
I know. But also creating a new table and copy the old data to the new does not work. – Elec Apr 28 '17 at 09:50
I've created a new result table like above only with the new column. Than tried to copy the data from the old table to the new table with this command `INSERT INTO results_copy (id, trace_id, config_id, cmake_flag_id, commit_id, platform_id, branch_id) SELECT id, resultA_id, resultB_id, resultC_flag_id, resultD_id, resultE_id, resultF_id FROM results;` The column will be left here. Than I got this error `Cannot add or update a child row: a foreign key constraint fails` – Elec Apr 28 '17 at 11:08

Can not add new column with foreign key

1 Answers1