CODEWITHSUNDEEP
mysqlsql
bart2puck
bart2puck

Reputation: 2522

error trying to alter tables to add foreign keys

I have built 2 tables. forms and items.

| forms | CREATE TABLE `forms` (
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`formName` varchar(64) COLLATE utf8_unicode_ci NOT NULL DEFAULT '',
`dueDate` date DEFAULT '0000-00-00',
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=84 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

items:

| items | CREATE TABLE `items` (
`formId` bigint(10) NOT NULL DEFAULT '0',
`id` bigint(10) NOT NULL AUTO_INCREMENT,
`itemName` varchar(32) COLLATE utf8_unicode_ci NOT NULL DEFAULT '', 
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=56 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci |

forms is the parent, items is the child.

I am trying to alter the tables to add a foreign key, so that if forms.id gets deleted, the items in items with formId=forms.id gets deleted as well.

My question is i cant get anything i do to work, what am i doing wrong?

ALTER TABLE items ADD CONSTRAINT FOREIGN KEY (formId) REFERENCES forms(id) ON DELETE CASCADE;
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`server_newTeamSales`.`#sql-64e0_28020de`, CONSTRAINT `#sql-64e0_28020de_ibfk_1` FOREIGN KEY (`formId`) REFERENCES `forms` (`id`) ON DELETE CASCADE)

ALTER TABLE forms ADD CONSTRAINT FOREIGN KEY (id) REFERENCES items(formId) ON DELETE CASCADE;
ERROR 1005 (HY000): Can't create table 'server_newTeamSales.#sql-64e0_28020de' (errno: 150)

I am not even sure which table needs the keys, foreign keys are new to me, and i cant understand the mysql docs on the dev site.

Upvotes: 0

Views: 47

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172438

For the first issue there is probably an issue with data which is present in the table. There is probably a value in the child table that does not exist in the parent table.

Check like this:

SELECT formId FROM items WHERE formId NOT IN (SELECT id FROM forms)

For the second error you may probably try by making formId a primary key in items table.

Upvotes: 1

Related Questions