mysql中字段约束unique,MySQL UNIQUE约束多列条件

Well, i have a table with 4 columns(id, event_id, group_id, isbn), where id is PK, event_id and group_id are FK's, and my problem is:

I need the isbn number to be unique for each event_id, let me give you some examples of some rows that should be possible and some that should not:

id | event_id | group_id | isbn

(1,1,1,123) ok

(2,1,2,123) ok

(3,1,4,123) ok

(4,1,7,1234) ok

(5,2,8,123) NOT OK, the 'isbn' must be unique for event_id('123' was already used in the first row with event_id = 1)

the group_id only appears once for each event_id, but if i make a unique contraint with the 3 columns i would be able to repeat the isbn just chaning the event_id, and i don't want that,once an isbn is used in an event_id it cant appear in another event_id, an event_id(let's say '1') can repeat the same ibsn as longe as it needs for each group_id

I know i kind of repeated the problem several times, but is a tricky question and i want to lower the chances of getting wrong answers

EDIT1: about @Andomar answer, the isbn must be related to the group_id by the cardinality (1,n) 1-isbn -> n-group_id and the structure in the answer don't do that

解决方案

What you describe is that event_id is dependant on the isbn . You need to normalize the table by splitting it into two:

(Corrected):

Remove Keep isbn in this - and add a FOREIGN KEY (event_id, isbn) constraint to the second table, below:

id | event_id | group_id | isbn

1 1 1 123

2 1 2 123

3 1 4 123

4 1 7 1234

5 2 8 123 --- not allowed by the FK constraint

And create a new table with isbn as the primary key and a UNIQUE (event_id, isbn) key (and two foreign keys: event_id to Event table and isbn to (Book?), if you have a table where isbn is the primary or unique key):

event_id | isbn

1 123

1 1234