列中的唯一值但仅对每个关联的外键唯一 [英] unique values in a column but only unique for each associated foreign key

问题描述

对于令人困惑的标题，我深表歉意，但我真的不知道如何用一句话描述这个问题.

I apologise for the confusing title but I'm really not sure how to describe this problem in one sentence.

我有一个 SQLite 数据库用于一般的小费竞赛.对于那些不熟悉这个概念的人，人们会在每轮体育比赛的每场比赛中给他们认为会获胜的人打赏"，而在比赛结束时提示最正确的人获胜(奖品可能是金钱、骄傲等).AFL 在澳大利亚非常受欢迎.

I have an SQLite database for a generalised tipping competition. For those unfamiliar with the concept, people "tip" who they think will win in each match of each round of a sporting competition, and the person with the most correct tips at the end of the competition wins (the prize may be money, pride, etc). It's very popular in Australia with the AFL.

基本结构是用户注册为所有者，然后他们可以创建任意数量的比赛，并且每个比赛都通过一个外键链接到他们.这是数据库架构的相关部分:

The basic structure is that users register as an owner, and they can then create as many competitions as they want, and each competition is linked to them with a foreign key. This is the relevant part of the database schema:

create table owner (
    owner_id INTEGER NOT NULL,
    owner_name VARCHAR NOT NULL,
    owner_nick VARCHAR UNIQUE NOT NULL,
    owner_email VARCHAR NOT NULL,
    owner_password VARCHAR NOT NULL,
    CONSTRAINT owner_pk PRIMARY KEY (owner_id)
);

create table comp (
    comp_id INTEGER NOT NULL,
    comp_owner_id INTEGER NOT NULL,
    comp_name VARCHAR UNIQUE NOT NULL,
    CONSTRAINT comp_pk PRIMARY KEY (comp_id),
    CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE
);

问题的要点是:我想对 comp 表的 comp_name 字段强制执行唯一约束(如上所示)，因为单个所有者不应该有两个或多个同名的比赛.但是，完全有可能两个不同的所有者想要两个名称相同的不同比赛(例如，两个办公室为同一个体育联盟举办小费比赛).是否可以使用 SQL 强制执行此操作，还是需要在其他地方手动执行?

The gist of the problem is this: I want to enforce a unique constraint on the comp_name field of the comp table (as shown above) because a single owner shouldn't have two or more competitions with the same name. However, it is entirely possible that two different owners would want two different competitions with the same name (for example, two offices running tipping competitions for the same sporting league). Is it possible to enforce this using SQL, or do I need to do the enforcement manually elsewhere?

一些注意事项:

• 我使用的是 SQLite 3.6.20，它具有适当的外键约束实施
• 永远不会在生产级应用程序中使用该数据库.这是一个私人项目.
• 该数据库正在基于 Web 的 PHP 应用程序中使用.
• 如果使用 SQLite 无法实现这一点，那么使用 MySQL 可以实现吗?

我觉得我应该指出我知道 SQLite 对更改表的支持非常有限，删除数据库并重新开始是一种非常好的选择.

I feel I should point out that I'm aware SQLite has very limited alter table support, and deleting the database and starting over is very much an option.

推荐答案

只需在这两个字段上创建一个 UNIQUE 约束，如下所示:

Just create a UNIQUE constraint on those two fields, like so:

create table comp (
    comp_id INTEGER NOT NULL,
    comp_owner_id INTEGER NOT NULL,
    comp_name VARCHAR NOT NULL,
    CONSTRAINT comp_unq UNIQUE (comp_owner_id, comp_name),
    CONSTRAINT comp_pk PRIMARY KEY (comp_id),
    CONSTRAINT comp_owner_fk FOREIGN KEY (comp_owner_id) REFERENCES owner (owner_id) NOT DEFERRABLE
);

这应该可以满足您的需求.

that should do what you want.

这篇关于列中的唯一值但仅对每个关联的外键唯一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！