[SQL] 어떻게 MySQL의 테이블에 사용자 정의 CHECK 제약 조건을 추가하는 방법은 무엇입니까?

어떻게 MySQL의 테이블에 사용자 정의 CHECK 제약 조건을 추가하는 방법은 무엇입니까?

나는이 테이블에 문제가 있어요

CREATE TABLE `Participants` (
  `meetid` int(11) NOT NULL,
  `pid` varchar(15) NOT NULL,
  `status` char(1) DEFAULT NULL,
  PRIMARY KEY (`meetid`,`pid`),
  CONSTRAINT `participants_ibfk_1` FOREIGN KEY (`meetid`) REFERENCES `Meetings` (`meetid`) ON DELETE CASCADE
  CONSTRAINT `participants_ibfk_2` CHECK (status IN ('a','d','u'))
  CONSTRAINT `participants_ibfk_3` CHECK (pid IN (SELECT name FROM Rooms) OR pid IN (SELECT userid FROM People))
);

나는 외래 키 제약 조건, 그리고 그 작품을 갖고 싶어. 그런 다음, 나는 또한 그것은 단지 값 'A', 'D'와 'U'를 취할 수 있도록 속성 상태에 제약 조건을 추가 할. 저를 열거 나 집합으로 필드를 설정하는 것은 불가능합니다.

이 코드는 MySQL의에서 작동하지 않는 사람이 왜 말해 줄래?

해결법

1. ==============================

   1.이 글에서 설명하고있는 바와 같이, 버전 8.0.16로 시작, MySQL은 CHECK 제약 조건에 대한 지원을 추가했습니다 :

   이 글에서 설명하고있는 바와 같이, 버전 8.0.16로 시작, MySQL은 CHECK 제약 조건에 대한 지원을 추가했습니다 :

   ALTER TABLE topic
   ADD CONSTRAINT post_content_check
   CHECK (
       CASE
           WHEN DTYPE = 'Post'
           THEN
               CASE
                   WHEN content IS NOT NULL
                   THEN 1
                   ELSE 0
               END
           ELSE 1
       END = 1
   );
   
   ALTER TABLE topic
   ADD CONSTRAINT announcement_validUntil_check
   CHECK (
       CASE
           WHEN DTYPE = 'Announcement'
           THEN
               CASE
                   WHEN validUntil IS NOT NULL
                   THEN 1
                   ELSE 0
               END
           ELSE 1
       END = 1
   );
   

   이전에는이 ​​삽입하기 전에 및 UPDATE 트리거하기 전에 사용하는 경우에만 사용할 수있었습니다 :

   CREATE
   TRIGGER post_content_check BEFORE INSERT
   ON topic
   FOR EACH ROW
   BEGIN
      IF NEW.DTYPE = 'Post'
      THEN
          IF NEW.content IS NULL
          THEN
              signal sqlstate '45000'
              set message_text = 'Post content cannot be NULL';
          END IF;
      END IF;
   END;
   
   CREATE
   TRIGGER post_content_update_check BEFORE UPDATE
   ON topic
   FOR EACH ROW
   BEGIN
      IF NEW.DTYPE = 'Post'
      THEN
          IF NEW.content IS NULL
          THEN
              signal sqlstate '45000'
              set message_text = 'Post content cannot be NULL';
          END IF;
      END IF;
   END;
   
   CREATE
   TRIGGER announcement_validUntil_check BEFORE INSERT
   ON topic
   FOR EACH ROW
   BEGIN
      IF NEW.DTYPE = 'Announcement'
      THEN
          IF NEW.validUntil IS NULL
          THEN
              signal sqlstate '45000'
              set message_text = 'Announcement validUntil cannot be NULL';
          END IF;
      END IF;
   END;
   
   CREATE
   TRIGGER announcement_validUntil_update_check BEFORE UPDATE
   ON topic
   FOR EACH ROW
   BEGIN
      IF NEW.DTYPE = 'Announcement'
      THEN
          IF NEW.validUntil IS NULL
          THEN
              signal sqlstate '45000'
              set message_text = 'Announcement validUntil cannot be NULL';
          END IF;
      END IF;
   END;
   

2. ==============================

   2.CHECK 제약 조건은 MySQL이 지원되지 않습니다. 당신이 그들을 정의 할 수 있지만 (MySQL을 5.7로) 아무것도하지 않습니다.

   CHECK 제약 조건은 MySQL이 지원되지 않습니다. 당신이 그들을 정의 할 수 있지만 (MySQL을 5.7로) 아무것도하지 않습니다.

   수동에서 :

   해결 방법은 트리거를 만드는 것입니다,하지만 그들은 작업은 쉬운 일이 아니다.

   당신이 오픈 소스가 지원하는 제약 조건을 확인하는 것이 RDBMS하려면, PostgreSQL을 시도. 실제로 아주 좋은 데이터베이스입니다.

3. ==============================

   3.여기 아무도 볼 수있는 체크 옵션은 MySQL의에서 CHECK 제약 조건에 대한 좋은 대안이 될 수 있다고 언급되지 왜 이해가 안 :

   여기 아무도 볼 수있는 체크 옵션은 MySQL의에서 CHECK 제약 조건에 대한 좋은 대안이 될 수 있다고 언급되지 왜 이해가 안 :

   CREATE VIEW name_of_view AS SELECT * FROM your_table
   WHERE <condition> WITH [LOCAL | CASCADED] CHECK OPTION;
   

   MySQL의 사이트에있는 문서가 있습니다 :보기 WITH CHECK OPTION 절

   DROP TABLE `Participants`;
   
   CREATE TABLE `Participants` (
     `meetid` int(11) NOT NULL,
     `pid` varchar(15) NOT NULL,
     `status` char(1) DEFAULT NULL check (status IN ('a','d','u')),
     PRIMARY KEY (`meetid`,`pid`)
   );
   
   -- should work
   INSERT INTO `Participants` VALUES (1,1,'a');
   -- should fail but doesn't because table check is not implemented in MySQL
   INSERT INTO `Participants` VALUES (2,1,'x');
   
   DROP VIEW vParticipants;
   CREATE VIEW vParticipants AS 
     SELECT * FROM Participants WHERE status IN ('a','d','u')
     WITH CHECK OPTION;
   
   -- should work
   INSERT INTO vParticipants VALUES (3,1,'a');
   -- will fail because view uses a WITH CHECK OPTION
   INSERT INTO vParticipants VALUES (4,1,'x');
   

   P.S :보기가 갱신되어야한다고 명심하십시오! MySQL의 업데이트 가능한 뷰를 참조하십시오 (감사 로미오 시에라 의견에 해명).

4. ==============================

   4.트리거 옆에있는 것과 같은 간단한 제약을 위해, 당신은 :

   트리거 옆에있는 것과 같은 간단한 제약을 위해, 당신은 :

   CONSTRAINT `participants_ibfk_2` 
     CHECK status IN ('a','d','u')
   

   당신이 참조 테이블에 상태에서 외부 키를 사용할 수 있습니다 (3 행으로 ParticipantStatus : 'A', 'D'를, 'U') :

   CONSTRAINT ParticipantStatus_Participant_fk
     FOREIGN KEY (status)
       REFERENCES ParticipantStatus(status) 
   

5. ==============================

   5.여기에 빠르고 쉽게 원하는 검사를 얻기의 방법입니다 :

   여기에 빠르고 쉽게 원하는 검사를 얻기의 방법입니다 :

   drop database if exists gtest;
   
   create database if not exists gtest;
   use gtest;
   
   create table users (
     user_id       integer unsigned not null auto_increment primary key,
     username      varchar(32) not null default '',
     password      varchar(64) not null default '',
     unique key ix_username (username)
   ) Engine=InnoDB auto_increment 10001;
   
   create table owners (
     owner_id      integer unsigned not null auto_increment primary key,
     ownername     varchar(32) not null default '',
     unique key ix_ownername (ownername)
   ) Engine=InnoDB auto_increment 5001;
   
   create table users_and_owners (
     id    integer unsigned not null primary key,
     name  varchar(32) not null default '',
     unique key ix_name(name)
   ) Engine=InnoDB;
   
   create table p_status (
     a_status      char(1) not null primary key
   ) Engine=InnoDB;
   
   create table people (
     person_id integer unsigned not null auto_increment primary key,
     pid       integer unsigned not null,
     name      varchar(32) not null default '',
     status    char(1) not null,
     unique key ix_name (name),
     foreign key people_ibfk_001 (pid) references users_and_owners(id),
     foreign key people_ibfk_002 (status) references p_status (a_status)
   ) Engine=InnoDB;
   
   create or replace view vw_users_and_owners as
   select 
     user_id id,
     username name
   from users
   union
   select 
     owner_id id,
     ownername name
   from owners
   order by id asc
   ;
   
   create trigger newUser after insert on users for each row replace into users_and_owners select * from vw_users_and_owners;
   create trigger newOwner after insert on owners for each row replace into users_and_owners select * from vw_users_and_owners;
   
   insert into users ( username, password ) values
   ( 'fred Smith', password('fredSmith')),
   ( 'jack Sparrow', password('jackSparrow')),
   ( 'Jim Beam', password('JimBeam')),
   ( 'Ted Turner', password('TedTurner'))
   ;
   
   insert into owners ( ownername ) values ( 'Tom Jones'),( 'Elvis Presley'),('Wally Lewis'),('Ted Turner');
   
   insert into people (pid, name, status) values ( 5001, 'Tom Jones', 1),(10002,'jack Sparrow',1),(5002,'Elvis Presley',1);
   

from https://stackoverflow.com/questions/7522026/how-do-i-add-a-custom-check-constraint-on-a-mysql-table by cc-by-sa and MIT license