MySQL UNIQUE约束多列条件 [英] MySQL UNIQUE Constraint multiple columns condition

问题描述

好吧，我有一个包含4列的表（ id ， event_id ， group_id ， isbn ），其中id为PK， event_id 和 group_id 是FK的，我的问题是：

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:

我需要每个 event_id的isbn数字是唯一的，让我举几个应该可行的行的例子和一些不应该的行：

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)

group_id 每个 event_id只显示一次，但是如果我对3列做出一个独特的约束，我将能够重复isbn只是在处理 event_id ，而我不希望这样，一次一个 isbn 用于 event_id 它不能出现在另一个 event_id ， event_id （假设'1'）可以重复相同的 ibsn 作为每个需要的长度 group_id

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：关于@Andomar的回答，isbn必须与基数相关联的group_id （1，n）1-isbn - > n-group_id，答案中的结构不这样做

推荐答案

您所描述的是 event_id 取决于 isbn 。您需要将表格分为两个来规范化表格：

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

（已更正）：

删除在此保留 isbn - 并添加 FOREIGN KEY（event_id，isbn）约束到第二个表，如下：

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

并创建一个新表 isbn as主键和 UNIQUE（event_id，isbn）键（和两个外键： event_id 到事件表和 isbn 到（预订？），如果你有一张桌子其中 isbn 是主键或唯一键）：

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 

这篇关于MySQL UNIQUE约束多列条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！