如何创建检查约束以允许表中的某些字符串-mysql 8

np8igboo 于 2021-07-24 发布在 Java

关注(0)|答案(2)|浏览(330)

我需要使只在表中存储'first'、'business'或'economy'的值成为可能。但是，当我尝试执行此操作时，错误显示为：
错误3819。违反了检查约束“seat\u check1”。我查看了insert语句，输入的值没有违反check约束。
到目前为止，我掌握的情况如下：

ALTER TABLE SEATING ADD CONSTRAINT seat_check1 CHECK (LIKE 'FIRST' OR 'BUSINESS' OR 'ECONOMY');

下面是我试图添加约束的表：

CREATE TABLE SEATING (
flightNumber    CHAR(6),            /* Flight number */
departAirport   VARCHAR(50),        /* Departure airport */
departTime      DATETIME,           /* Departure date and time */
class           CHAR(10),           /* class of the seat */
available       DECIMAL(3),         /* Available number of Seat so far */
CONSTRAINT SEATING_PK PRIMARY KEY(flightNumber, departAirport, departTime, class),
CONSTRAINT SEATING_FK1 FOREIGN KEY(flightNumber, departAirport, departTime) REFERENCES FLIGHT(flightNumber, departAirport, departTime));

以下是insert语句：

INSERT INTO SEATING VALUES ('QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
INSERT INTO SEATING VALUES ('QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'BUSINESS', 10);
INSERT INTO SEATING VALUES ('QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 176);
INSERT INTO SEATING VALUES ('QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 15);
INSERT INTO SEATING VALUES ('QF140', 'AKL', STR_TO_DATE('18/05/2019 06:30', '%d/%m/%Y %H:%i'), 'BUSINESS', 40);
INSERT INTO SEATING VALUES ('QF140', 'AKL', STR_TO_DATE('18/05/2019 06:30', '%d/%m/%Y %H:%i'), 'ECONOMY', 140);
INSERT INTO SEATING VALUES ('JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'FIRST', 10);
INSERT INTO SEATING VALUES ('JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 90);
INSERT INTO SEATING VALUES ('QF860', 'SYD', STR_TO_DATE('23/04/2019 07:10', '%d/%m/%Y %H:%i'), 'FIRST', 16);
INSERT INTO SEATING VALUES ('QF860', 'SYD', STR_TO_DATE('23/04/2019 07:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 35);
INSERT INTO SEATING VALUES ('VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 21);
INSERT INTO SEATING VALUES ('VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 08:50', '%d/%m/%Y %H:%i'), 'BUSINESS', 3);
INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 08:50', '%d/%m/%Y %H:%i'), 'ECONOMY', 32);
INSERT INTO SEATING VALUES ('QF862', 'SYD', STR_TO_DATE('18/05/2019 11:45', '%d/%m/%Y %H:%i'), 'BUSINESS', 0);
INSERT INTO SEATING VALUES ('QF862', 'SYD', STR_TO_DATE('18/05/2019 11:45', '%d/%m/%Y %H:%i'), 'ECONOMY', 2);
INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 13:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 21);
INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 13:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 67);
INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('26/04/2019 13:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 12);
INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('26/04/2019 13:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 32);
INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('10/05/2019 15:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 10);
INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('10/05/2019 15:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 22);
INSERT INTO SEATING VALUES ('VA500', 'OOL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 1);
INSERT INTO SEATING VALUES ('VA500', 'OOL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 15);
INSERT INTO SEATING VALUES ('JQ401', 'OOL', STR_TO_DATE('18/05/2019 06:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 7);
INSERT INTO SEATING VALUES ('JQ401', 'OOL', STR_TO_DATE('18/05/2019 06:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 47);
INSERT INTO SEATING VALUES ('JQ403', 'OOL', STR_TO_DATE('18/05/2019 07:55', '%d/%m/%Y %H:%i'), 'BUSINESS', 20);
INSERT INTO SEATING VALUES ('JQ403', 'OOL', STR_TO_DATE('18/05/2019 07:55', '%d/%m/%Y %H:%i'), 'ECONOMY', 52);
INSERT INTO SEATING VALUES ('JQ409', 'OOL', STR_TO_DATE('18/05/2019 10:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 9);
INSERT INTO SEATING VALUES ('JQ409', 'OOL', STR_TO_DATE('18/05/2019 10:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 74);
INSERT INTO SEATING VALUES ('JQ501', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 28);
INSERT INTO SEATING VALUES ('JQ501', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 243);
INSERT INTO SEATING VALUES ('QF401', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 26);
INSERT INTO SEATING VALUES ('QF401', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 198);
INSERT INTO SEATING VALUES ('VA808', 'SYD', STR_TO_DATE('05/05/2019 07:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 16);
INSERT INTO SEATING VALUES ('VA808', 'SYD', STR_TO_DATE('05/05/2019 07:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
INSERT INTO SEATING VALUES ('QF400', 'MEL', STR_TO_DATE('11/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 36);
INSERT INTO SEATING VALUES ('QF400', 'MEL', STR_TO_DATE('11/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 268);
INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'FIRST', 5);
INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 12);
INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 132);
INSERT INTO SEATING VALUES ('VA815', 'MEL', STR_TO_DATE('11/05/2019 07:30', '%d/%m/%Y %H:%i'), 'BUSINESS', 0);
INSERT INTO SEATING VALUES ('VA815', 'MEL', STR_TO_DATE('11/05/2019 07:30', '%d/%m/%Y %H:%i'), 'ECONOMY', 0);
INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'BUSINESS', 45);
INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'ECONOMY', 320);
INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'FIRST', 8);
INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'BUSINESS', 45);
INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'ECONOMY', 150);
INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'FIRST', 14);
INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'BUSINESS', 18);
INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'ECONOMY', 22);
INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'FIRST', 5);
INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 16);
INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 25);
INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'FIRST', 3);
INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 1);
INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 10);
INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'FIRST', 0);

sql mysql create-table sql-insert check-constraints

来源：https://stackoverflow.com/questions/62516860/how-to-create-a-check-constraint-to-allow-certain-strings-in-a-table-mysql-8

2条答案

按热度按时间

sbdsn5lh1#

这应该简单到：

ALTER TABLE SEATING ADD CONSTRAINT seat_check1 CHECK (class IN ('FIRST', 'BUSINESS', 'ECONOMY'));

或者作为你生命的一部分 CREATE TABLE 声明：

CREATE TABLE SEATING (
    flightNumber    CHAR(6),        
    departAirport   VARCHAR(50),
    departTime      DATETIME,
    class           CHAR(10) CHECK(class in ('FIRST', 'BUSINESS', 'ECONOMY')),
    available       DECIMAL(3),
    CONSTRAINT SEATING_PK PRIMARY KEY(flightNumber, departAirport, departTime, class),
    CONSTRAINT SEATING_FK1 FOREIGN KEY(flightNumber, departAirport, departTime) REFERENCES FLIGHT(flightNumber, departAirport, departTime)
);

赞(0）回复(0）举报 2021-07-24

fkvaft9z2#

我想你不想用 LIKE 我想你失去了列名。试着这样做：

ALTER TABLE SEATING ADD CONSTRAINT seat_check1 CHECK (class = 'FIRST' OR class = 'BUSINESS' OR class = 'ECONOMY');

赞(0）回复(0）举报 2021-07-24

我来回答

如何创建检查约束以允许表中的某些字符串-mysql 8

2条答案

相关问题

热门标签

最新问答