When creating a constraint for a hierarchy I get an error, it doesn't let me make inserts I leave the code in case someone can give me a hand:
CREATE TABLE JUGADORES (
Id_jugador NUMBER (4) PRIMARY KEY,
Nombre VARCHAR2(30) NOT NULL,
Ape1 VARCHAR2(40) NOT NULL,
Ape2 VARCHAR2(20) NOT NULL,
Telefono VARCHAR2(15) NOT NULL,
Elo NUMBER(5) NOT NULL,
Tipo VARCHAR2(10) CHECK (Tipo IN('socios', 'no_socios')) NOT NULL,
Cuota_membresia DECIMAL(10, 2) DEFAULT 0,
Motivo_participacion VARCHAR2 (100) DEFAULT '',
Id_torneo NUMBER(4) NOT NULL,
Fecha_inscripcion DATE NOT NULL,
CONSTRAINT cuota_socio CHECK (Tipo = 'socios' and Cuota_membresia IS NOT NULL ),
CONSTRAINT mot_no_socio CHECK (Tipo = 'no_socios' and Motivo_participacion IS NOT NULL ),
CONSTRAINT id_jugador_CK CHECK(id_jugador BETWEEN 1000 AND 3000)
);
--Inserciones tabla jugadores
INSERT INTO JUGADORES (Id_jugador, Nombre, Ape1, Ape2, Telefono, Elo, Tipo, Cuota_membresia, Motivo_participacion, Id_torneo, Fecha_inscripcion)
VALUES (
1001, 'Pepe', 'Gomez', 'Fran', '587455596', 1500, 'socios', 10.5, '', 1, TO_DATE('2023-12-09', 'YYYY-MM-DD')
);
I try to ensure that when is "socio" , only the "cuota_membresia" is required and when is "no_socio", only the "motivo_participacion" is not null.
The error message is:
Error que empieza en la línea: 83 del comando :
INSERT INTO JUGADORES (
Id_jugador, Nombre,
Ape1, Ape2,
Telefono, Elo,
Tipo, Cuota_membresia,
Motivo_participacion, Id_torneo,
Fecha_inscripcion)
VALUES (
1001, 'Pepe',
'Gomez', 'Fran',
'587455596', 1500,
'socios', 10.5,
'', 1,
TO_DATE('2023-12-09', 'YYYY-MM-DD') )
Informe de error -
ORA-02290: check constraint
(DAM.MOT_NO_SOCIO) violated
The two check constraints contradict each other. Combining them into one condition using the logical or operator should give you the desired behavior:
CONSTRAINT socio CHECK ((Tipo = 'socios' and Cuota_membresia IS NOT NULL) OR (Tipo = 'no_socios' and Motivo_participacion IS NOT NULL))