is it possible to have a unique on a group of rows instead of entire table?

Question

i have a table like

CREATE TABLE IF NOT EXISTS grouped_executions (
    id             INTEGER UNSIGNET NOT NULL ,
    execution_id   INTEGER UNSIGNED NOT NULL REFERENCES execution.execution_id ,
    president      BOOLEAN NOT NULL DEFAULT 0

    PRIMARY KEY ( id, execution_id )
) ENGINE = InnoDB ;

all i want is to make president unique in a group of rows with same id.

for example: i have such data:

id | execution_id | president
= - = - = - = - = - = - = - =
1  | 1            | 0
1  | 2            | 1
1  | 3            | 0
1  | 4            | 0

i want mysql prevents inserting new row with id = 1 & president = 1

( of course i can make another table that holds president of a group but is above structure possible? )

I think a better design is like you said, set it in another table. — Danosaure, Dec 07 '10 at 09:03

score 3 · Answer 1 · edited May 23 '17 at 12:18

3

Yes, you can add a unique-constraint for the combination of the two columns.

How do I specify unique constraint for multiple columns in MySQL?

edited May 23 '17 at 12:18

Community

1
1

answered Dec 07 '10 at 08:36

David Hedlund

128,221
31
203
222

but i have many rows with id = 1 & president = 0, if i do "UNIQUE KEY `grouped_executions_id_president` ( `id`, `president` )" i can't have em – everplays Dec 07 '10 at 08:40
oh, sorry, I got your question wrong, then. I don't know that you can specify uniqueness only for specific values like that in MySQL (I could be wrong). A workaround might be using a trigger to validate an insert or update. – David Hedlund Dec 07 '10 at 08:50

score 1 · Accepted Answer · answered Dec 07 '10 at 09:11

You may want to try to set default NULL for president as unique key allows multiple NULL values, but then, it kind of make no sense to have a bool with only one 1 and many NULLs, but no 0. If you don't want to set other mechanisms like triggers (suggested by @David Hedlund), you better of with another table for that relationship (as you mentionned it). This way, if you one day wants a unique "secretary" or "treasury", it would be easy to define a new table instead of having that trigger (or another one) verify for this relationship.

score -1 · Answer 3 · edited Aug 17 '12 at 21:29

-1

Have you tried this?

ALTER TABLE grouped_executions ADD CONSTRAINT PRIMARY KEY (id, president);

edited Aug 17 '12 at 21:29

Conner

30,144
8
52
73

answered Dec 07 '10 at 08:45

AnD

3,060
8
35
63

mysql> ALTER TABLE grouped_executions ADD CONSTRAINT PRIMARY KEY (id, president); ERROR 1068 (42000): Multiple primary key defined – everplays Dec 07 '10 at 08:55

is it possible to have a unique on a group of rows instead of entire table?

3 Answers3