Two columns in same table and same foreign key

Question

I'm developing an application, actually a billing system. Here accountant can add invoice for a client.

I have two tables, users and invoices:

invoices (user_id, created_by)
users (id)

Invoices has two columns, user_id and created_by, I want both to be linked with id of the users table.

Already user_id has been added as foreign key. Now I'm trying to add created_by as foreign key. So issued following command:

ALTER TABLE `invoices`
ADD FOREIGN KEY (`created_by`) REFERENCES `secureap_maind`.`users` (`id`)
ON DELETE RESTRICT ON UPDATE RESTRICT;

And I'm getting an error message.

#1452 - Cannot add or update a child row: a foreign key constraint fails (secureap_maind.#sql-3717_a323d, CONSTRAINT #sql-3717_a323d_ibfk_2 FOREIGN KEY (created_by) REFERENCES users (id))

I'm not sure if I can add two columns as foreign key. IF possible, can you please advice to do that?

Thanks in advance.

Answer 1

This SO post implies that removing your ON DELETE RESTRICT clause from the ALTER TABLE statement might solve your problem. Try running this query instead:

ALTER TABLE `invoices`
ADD FOREIGN KEY (`created_by`) REFERENCES `secureap_maind`.`users`(`id`)

I am assuming that the invoices table was created using InnoDB rather than MyISAM, the latter which does not enforce foreign keys.