mysql - una - tipos de restricciones en base de datos

¿Cómo agrego una restricción de verificación a una tabla?

Question

Estoy teniendo problemas con esta tabla

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))
);

Quiero tener una restricción de clave externa, y eso funciona. Entonces quiero agregar una restricción al status del atributo para que solo pueda tomar los valores ''a'', ''d'' y ''u''. No me es posible configurar el campo como Enum o set .

¿Alguien puede decirme por qué este código no funciona en MySQL?

Answer 1

Además de los desencadenantes, para restricciones simples como la que tienes:

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

puede usar una Foreign Key de status a una tabla de referencia (estado ParticipantStatus con 3 filas: ''a'',''d'',''u'' ):

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

Answer 2

Aquí hay una forma de obtener los cheques que quería rápida y fácilmente:

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);

Answer 3

No entiendo por qué nadie aquí ha mencionado que VER CON OPCIÓN DE VERIFICACIÓN puede ser una buena alternativa a la RESTRICCIÓN DE VERIFICACIÓN en MySQL:

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

Hay un documento en el sitio de MySQL: La cláusula View 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'');

Answer 4

CHECK restricciones CHECK no son compatibles con MySQL. Puede definirlos, pero no hacen nada (a partir de MySQL 5.7).

Del manual :

La cláusula CHECK es analizada pero ignorada por todos los motores de almacenamiento.

La solución alternativa es crear triggers , pero no son lo más fácil para trabajar.

Si desea un RDBMS de código abierto que admita restricciones CHECK , intente con PostgreSQL . En realidad es una muy buena base de datos.