I tried to insert some data in a database with postgresql but still showing the same message:
ERROR: new row for relation "empleados" violates check constraint "ck_empleados_documento" DETAIL: Failing row contains (13, 22222222, f, Lopez, Ana, Colon 123, 1, 2, casado , 1990-10-10).
I do not know where or what the error is nor did I find anything that solved this. This is what i try to insert:
insert into empleados (documento, sexo, apellido, nombre, domicilio, idSecc, cantidadhijos, estadocivil, fechaingreso) values('22222222','f','Lopez','Ana','Colon 123',1,2,'casado','1990-10-10');
and this is the structure of the table:
CREATE TABLE public.empleados
(
idempleado integer NOT NULL DEFAULT nextval('empleados_idempleado_seq'::regclass),
documento character(8),
sexo character(1),
apellido character varying(20),
nombre character varying(20),
domicilio character varying(30),
idsecc smallint NOT NULL,
cantidadhijos smallint,
estadocivil character(10),
fechaingreso date,
CONSTRAINT pk_empleados PRIMARY KEY (idempleado),
CONSTRAINT fk_empleados_idsecc FOREIGN KEY (idsecc)
REFERENCES public.puestos (idpuesto) MATCH SIMPLE
ON UPDATE CASCADE ON DELETE NO ACTION,
CONSTRAINT uq_empleados_documento UNIQUE (documento),
CONSTRAINT ck_empleados_documento CHECK (documento ~~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text),
CONSTRAINT ck_empleados_estadocivil CHECK (estadocivil = ANY (ARRAY['casado'::bpchar, 'divorciado'::bpchar, 'soltero'::bpchar, 'viudo'::bpchar])),
CONSTRAINT ck_empleados_hijos CHECK (cantidadhijos >= 0),
CONSTRAINT ck_empleados_sexo CHECK (sexo = ANY (ARRAY['f'::bpchar, 'm'::bpchar]))
)
The error message says your row violates check constraint "ck_empleados_documento"
.
ck_empleados_documento
is defined as
CONSTRAINT ck_empleados_documento CHECK (documento ~~ '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text)
According to https://www.postgresql.org/docs/current/static/functions-matching.html#FUNCTIONS-LIKE:
The operator
~~
is equivalent toLIKE
So your constraint really means
documento LIKE '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'::text
From the same page:
string
LIKE
pattern
If pattern does not contain percent signs or underscores, then the pattern only represents the string itself
Your pattern doesn't contain %
or _
, so it is equivalent to
documento = '[0-9][0-9][0-9][0-9][0-9][0-9][0-9][0-9]'
This can never be true because documento
is only 8 characters long.
You probably want to do this instead:
documento SIMILAR TO '[0-9]{8}'
SIMILAR TO
uses SQL regexes and understands character classes such as [0-9]
.