Insert to an UUID column with a foreign constraint fails

988 views Asked by At

I have the following table structure & data:

votes table

season | episode | UUID    | vote
1      | 1       | X-X-X-X | 1
1      | 1       | Y-Y-Y-Y | -1

users table

UUID    | name
X-X-X-X | Jon
Y-Y-Y-Y | Sam
Z-Z-Z-Z | Tim

In the votes table, after running

ALTER TABLE votes ADD PRIMARY KEY (season, episode, UUID);
ALTER TABLE votes ADD INDEX (UUID);
ALTER TABLE votes ADD FOREIGN KEY (UUID) REFERENCES user (UUID)
                           ON DELETE CASCADE ON UPDATE CASCADE;

and trying to insert an entry like this:

season, episode, UUID,      vote
1,      1,       'Z-Z-Z-Z', 1

into said table, the following error greeted me:

Cannot add or update a child row: a foreign key constraint fails (voting, CONSTRAINT `constraint_name` FOREIGN KEY (UUID) REFERENCES users (UUID) ON DELETE CASCADE ON UPDATE CASCADE)

I'd like to keep the UUID linked to the users table while not allowing multiple votes per same season - episode - UUID combination, how can I achieve this?

1

There are 1 answers

0
SeinopSys On BEST ANSWER

The issue was that I was adding the data using phpMyAdmin's Insert tab, and it automatically selected UUID from the Functions drop-down, which altered the data I wanted to insert. Setting the drop-down to an empty value left my inserted value alone and it was added just fine.