Constraint not working as desired for my INSERT?-CodePudding

I am creating a Table named "Cliente" with some constraints on it as it follows:

CREATE TABLE public."Cliente" (
    "K_CODCLIENTE" numeric(5) NOT NULL,
    "N_NOMBRE1" varchar(15) NOT NULL,
    "N_NOMBRE2" varchar(15) NOT NULL,
    "N_APELLIDO1" varchar(15) NOT NULL,
    "N_APELLIDO2" varchar(15),
    "N_DIRECCION" varchar(50) NOT NULL,
    "Q_TELEFONO" numeric(10) NOT NULL,
    "K_CODREF" numeric(5),
    "I_TIPOID" varchar(2) NOT NULL,
    "Q_IDENTIFICACION" varchar(10) NOT NULL,
    CONSTRAINT "PK_Cliente" PRIMARY KEY ("K_CODCLIENTE"),
    CONSTRAINT "UQ_ID_TIPOID_CLIENTE" UNIQUE ("I_TIPOID","Q_IDENTIFICACION"),
    CONSTRAINT "CK_CODCLIENTE" CHECK ("K_CODCLIENTE" >= 100),
    CONSTRAINT "CK_Q_IDENTIFICACION" CHECK ("Q_IDENTIFICACION" IN ('CC', 'PA', 'CE', 'NI', 'OT'))
);

When I try to insert some values on it:

INSERT INTO "Cliente"
VALUES ('101','Juan','Felipe','Ortiz','Rojas','AK 15 no. 28-05','3101125507',null,'CC','51111111');

I get the following error (in PostgreSQL 14, on Fedora):

[23514] ERROR: new row for relation "Cliente" violates check constraint "CK_Q_IDENTIFICACION"  
Detail: Failing row contains (101, Juan, Felipe, Ortiz, Rojas, AK 15 no. 28-05, 3101125507, null, CC, 51111111).

I am trying to restrict the "Q_IDENTIFICACION" column so it can only be filled with 'CC', 'PA', 'CE, 'NI' or 'OT'.

Maybe I'm doing something wrong when declaring the constraint "CK_Q_IDENTIFICACION"?

CodePudding user response：

Seems like you messed up the mapping of values and are trying to insert '51111111' to "Q_IDENTIFICACION".

Consider this more revealing variant with a formatted list of target columns:

INSERT INTO "Cliente"
       ("K_CODCLIENTE", "N_NOMBRE1", "N_NOMBRE2", "N_APELLIDO1", "N_APELLIDO2", "N_DIRECCION"    , "Q_TELEFONO", "K_CODREF", "I_TIPOID", "Q_IDENTIFICACION")
VALUES ('101'         , 'Juan'     ,'Felipe'    , 'Ortiz'      , 'Rojas'      , 'AK 15 no. 28-05', '3101125507', NULL      , 'CC'      , '51111111');       -- !

Maybe you want to switch the last two column names in the table definition - and (not) adapt the VALUES list in the INSERT accordingly? (varchar(2) vs. varchar(10) seems switched as well.)

For persisted code, it's generally advisable to spell out target columns in an INSERT command in any case.

Asides:

Reconsider all these pesky double-quoted upper case identifiers. See:

Are PostgreSQL column names case-sensitive?

Consider plain type text instead of varchar(n) with strikingly tight character limits. See:

Any downsides of using data type "text" for storing strings?