25 lines
614 B
MySQL
25 lines
614 B
MySQL
|
-- add constraints on votes table
|
||
|
|
CREATE TABLE votes_new
|
||
|
|
(
|
||
|
|
decision INTEGER NOT NULL REFERENCES decisions (id),
|
||
|
|
voter INTEGER NOT NULL REFERENCES voters (id),
|
||
|
|
vote INTEGER NOT NULL,
|
||
|
|
voted DATETIME NOT NULL,
|
||
|
|
notes TEXT NOT NULL DEFAULT '',
|
||
|
|
PRIMARY KEY (decision, voter)
|
||
|
|
);
|
||
|
|
INSERT INTO votes_new (decision, voter, vote, voted, notes)
|
||
|
|
SELECT decision,
|
||
|
|
voter,
|
||
|
|
vote,
|
||
|
|
voted,
|
||
|
|
notes
|
||
|
|
FROM votes
|
||
|
|
GROUP BY decision, voter
|
||
|
|
HAVING MAX(voted) = voted;
|
||
|
|
|
||
|
|
ALTER TABLE votes
|
||
|
|
RENAME TO votes_orig_with_duplicates;
|
||
|
|
ALTER TABLE votes_new
|
||
|
|
RENAME TO votes;
|