0

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

Murilo
  • 580
  • 5
  • 21

2 Answers2

2

To keep the (event_id, isbn) combination unique:

create unique index UX_YourTable_EventIsbn on YourTable(event_id, isbn)

Per the comment, if you'd like to have only one event per isbn, you could redesign the table relation. Create a table called isbn that has a a primary key isbn and a field called event_id. That way, an ISBN can have zero or one events associated with it.

isbn          isbn primary key, event_id
other_table   id, event_id, group_id

A query would look up the isbns for an event in the isbn table.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • how this index prevents adding once 1, 123 and then 2, 123 for event_id, isbn? It would simply prevent adding 1,123 twice which far different from the question. – Nedret Recep Oct 25 '12 at 18:01
  • @NedretRecep: Good point, didn't read that in the question. I'll update the answer. – Andomar Oct 25 '12 at 18:03
2

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
ypercubeᵀᴹ
  • 113,259
  • 19
  • 174
  • 235
  • Acctually worked, sorry, i had my second table with event_id marked as signed and in my first table it was unsigned so the columns were different, that is why i could not set it as foreign key, now i managed to do it, thanks – Murilo Oct 25 '12 at 19:35