MySQL唯一约束允许单行组合 [英] Mysql unique constraint allowing single row for a combination

问题描述

是否可以有一个唯一的约束，使得一个特定的列只有一个值一次?

Is it possible to have a unique constraint such that one particular column has a value only once?

例如

-----------------------    
name | price | default
-----------------------
XYZ  |  20   | TRUE
-----------------------
XYZ  |  30   | FALSE
-----------------------
XYZ  |  40   | FALSE
-----------------------
ABC  | 50    | FALSE
-----------------------

因此在上表中，对于特定的name值，default值只能为TRUE一次.并将对名称&价格栏.

So in above table, for a particular name value, default value can be TRUE only once. And will have a unique constraint on name & price columns.

这可能吗?

推荐答案

一个 normal 执行此操作的方法是提取一个单独的表以保存默认价格:

A normal way to do this is to extract a separate table to hold the default price :

CREATE TABLE price (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name, price)
) ;

CREATE TABLE defaultPrice (
    name VARCHAR(255),
    price INT,
    PRIMARY KEY (name),
    FOREIGN KEY(name, price) REFERENCES price(name, price)
);

大多数人会建议引入代理密钥:

Most people will advise introducing surrogate keys:

CREATE TABLE item (
    id INT PRIMARY KEY,
    name VARCHAR(255),
    UNIQUE(name)
);

CREATE TABLE price (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId, price),
    FOREIGN KEY (itemId) REFERENCES item (id)
) ;

CREATE TABLE defaultPrice (
    itemId INT,
    price INT,
    PRIMARY KEY (itemId),
    FOREIGN KEY (itemId, price) REFERENCES price (itemId, price)
);

这篇关于MySQL唯一约束允许单行组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！