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.