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?
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.