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

np8igboo  于 2021-07-24  发布在  Java
关注(0)|答案(2)|浏览(391)

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

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

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

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

以下是insert语句:

  1. INSERT INTO SEATING VALUES ('QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
  2. INSERT INTO SEATING VALUES ('QF8764', 'SYD', STR_TO_DATE('10/05/2019 08:45', '%d/%m/%Y %H:%i'), 'BUSINESS', 10);
  3. INSERT INTO SEATING VALUES ('QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 176);
  4. INSERT INTO SEATING VALUES ('QF322', 'AKL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 15);
  5. INSERT INTO SEATING VALUES ('QF140', 'AKL', STR_TO_DATE('18/05/2019 06:30', '%d/%m/%Y %H:%i'), 'BUSINESS', 40);
  6. INSERT INTO SEATING VALUES ('QF140', 'AKL', STR_TO_DATE('18/05/2019 06:30', '%d/%m/%Y %H:%i'), 'ECONOMY', 140);
  7. INSERT INTO SEATING VALUES ('JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'FIRST', 10);
  8. INSERT INTO SEATING VALUES ('JQ402', 'SYD', STR_TO_DATE('23/04/2019 07:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 90);
  9. INSERT INTO SEATING VALUES ('QF860', 'SYD', STR_TO_DATE('23/04/2019 07:10', '%d/%m/%Y %H:%i'), 'FIRST', 16);
  10. INSERT INTO SEATING VALUES ('QF860', 'SYD', STR_TO_DATE('23/04/2019 07:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 35);
  11. INSERT INTO SEATING VALUES ('VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 21);
  12. INSERT INTO SEATING VALUES ('VA505', 'SYD', STR_TO_DATE('24/04/2019 07:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
  13. INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 08:50', '%d/%m/%Y %H:%i'), 'BUSINESS', 3);
  14. INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 08:50', '%d/%m/%Y %H:%i'), 'ECONOMY', 32);
  15. INSERT INTO SEATING VALUES ('QF862', 'SYD', STR_TO_DATE('18/05/2019 11:45', '%d/%m/%Y %H:%i'), 'BUSINESS', 0);
  16. INSERT INTO SEATING VALUES ('QF862', 'SYD', STR_TO_DATE('18/05/2019 11:45', '%d/%m/%Y %H:%i'), 'ECONOMY', 2);
  17. INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 13:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 21);
  18. INSERT INTO SEATING VALUES ('JQ404', 'SYD', STR_TO_DATE('26/04/2019 13:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 67);
  19. INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('26/04/2019 13:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 12);
  20. INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('26/04/2019 13:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 32);
  21. INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('10/05/2019 15:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 10);
  22. INSERT INTO SEATING VALUES ('VA523', 'SYD', STR_TO_DATE('10/05/2019 15:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 22);
  23. INSERT INTO SEATING VALUES ('VA500', 'OOL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 1);
  24. INSERT INTO SEATING VALUES ('VA500', 'OOL', STR_TO_DATE('18/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 15);
  25. INSERT INTO SEATING VALUES ('JQ401', 'OOL', STR_TO_DATE('18/05/2019 06:10', '%d/%m/%Y %H:%i'), 'BUSINESS', 7);
  26. INSERT INTO SEATING VALUES ('JQ401', 'OOL', STR_TO_DATE('18/05/2019 06:10', '%d/%m/%Y %H:%i'), 'ECONOMY', 47);
  27. INSERT INTO SEATING VALUES ('JQ403', 'OOL', STR_TO_DATE('18/05/2019 07:55', '%d/%m/%Y %H:%i'), 'BUSINESS', 20);
  28. INSERT INTO SEATING VALUES ('JQ403', 'OOL', STR_TO_DATE('18/05/2019 07:55', '%d/%m/%Y %H:%i'), 'ECONOMY', 52);
  29. INSERT INTO SEATING VALUES ('JQ409', 'OOL', STR_TO_DATE('18/05/2019 10:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 9);
  30. INSERT INTO SEATING VALUES ('JQ409', 'OOL', STR_TO_DATE('18/05/2019 10:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 74);
  31. INSERT INTO SEATING VALUES ('JQ501', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 28);
  32. INSERT INTO SEATING VALUES ('JQ501', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 243);
  33. INSERT INTO SEATING VALUES ('QF401', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 26);
  34. INSERT INTO SEATING VALUES ('QF401', 'SYD', STR_TO_DATE('05/05/2019 06:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 198);
  35. INSERT INTO SEATING VALUES ('VA808', 'SYD', STR_TO_DATE('05/05/2019 07:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 16);
  36. INSERT INTO SEATING VALUES ('VA808', 'SYD', STR_TO_DATE('05/05/2019 07:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 120);
  37. INSERT INTO SEATING VALUES ('QF400', 'MEL', STR_TO_DATE('11/05/2019 06:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 36);
  38. INSERT INTO SEATING VALUES ('QF400', 'MEL', STR_TO_DATE('11/05/2019 06:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 268);
  39. INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'FIRST', 5);
  40. INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'BUSINESS', 12);
  41. INSERT INTO SEATING VALUES ('QF408', 'MEL', STR_TO_DATE('11/05/2019 07:00', '%d/%m/%Y %H:%i'), 'ECONOMY', 132);
  42. INSERT INTO SEATING VALUES ('VA815', 'MEL', STR_TO_DATE('11/05/2019 07:30', '%d/%m/%Y %H:%i'), 'BUSINESS', 0);
  43. INSERT INTO SEATING VALUES ('VA815', 'MEL', STR_TO_DATE('11/05/2019 07:30', '%d/%m/%Y %H:%i'), 'ECONOMY', 0);
  44. INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'BUSINESS', 45);
  45. INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'ECONOMY', 320);
  46. INSERT INTO SEATING VALUES ('CA3101', 'SYD', STR_TO_DATE('26/05/2019 22:15', '%d/%m/%Y %H:%i'), 'FIRST', 8);
  47. INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'BUSINESS', 45);
  48. INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'ECONOMY', 150);
  49. INSERT INTO SEATING VALUES ('QF129', 'SYD', STR_TO_DATE('26/05/2019 09:35', '%d/%m/%Y %H:%i'), 'FIRST', 14);
  50. INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'BUSINESS', 18);
  51. INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'ECONOMY', 22);
  52. INSERT INTO SEATING VALUES ('QF4014', 'PEK', STR_TO_DATE('10/05/2019 07:35', '%d/%m/%Y %H:%i'), 'FIRST', 5);
  53. INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 16);
  54. INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 25);
  55. INSERT INTO SEATING VALUES ('QF497', 'SYD', STR_TO_DATE('26/05/2019 22:05', '%d/%m/%Y %H:%i'), 'FIRST', 3);
  56. INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'BUSINESS', 1);
  57. INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'ECONOMY', 10);
  58. INSERT INTO SEATING VALUES ('CA2842', 'PEK', STR_TO_DATE('10/05/2019 22:05', '%d/%m/%Y %H:%i'), 'FIRST', 0);
sbdsn5lh

sbdsn5lh1#

这应该简单到:

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

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

  1. CREATE TABLE SEATING (
  2. flightNumber CHAR(6),
  3. departAirport VARCHAR(50),
  4. departTime DATETIME,
  5. class CHAR(10) CHECK(class in ('FIRST', 'BUSINESS', 'ECONOMY')),
  6. available DECIMAL(3),
  7. CONSTRAINT SEATING_PK PRIMARY KEY(flightNumber, departAirport, departTime, class),
  8. CONSTRAINT SEATING_FK1 FOREIGN KEY(flightNumber, departAirport, departTime) REFERENCES FLIGHT(flightNumber, departAirport, departTime)
  9. );
fkvaft9z

fkvaft9z2#

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

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

相关问题