Constraint not working as desired for my INSERT?

Question

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"?

Erwin Brandstetter · Accepted Answer · 2022-11-01T23:22:57.013

2

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?

edited Nov 01 '22 at 23:22

answered Nov 01 '22 at 22:58

Erwin Brandstetter

605,456
145
1,078
1,228

Thanks but it did not work. I still get the [23514] ERROR using that `INSERT`. – Smooth Researcher Nov 01 '22 at 23:07
In regards of the double-quoted upper case identifiers, the script was generated by pgmodeler. I'm not really sure why it generated it like that. I usually use lower case identifiers when working in PostgreSQL. – Smooth Researcher Nov 01 '22 at 23:09
I did not fix the order of columns. Just spelled it out for you to see. I am unable to tell how it *should* be. Possibly switch the last two values, or the last two column names in the table definition? Added a bit above. – Erwin Brandstetter Nov 01 '22 at 23:20
Sir Erwin, I have to ask one MCQ question from you about SQL is **What is the meaning of "GROUP BY" clause in MySQL?** and the options are : `(a) Group data by column values` `(b) Group data by column and row values` `(c) Group data by row values` `(d) None of the above` which option is correct here? – Sunderam Dubey Nov 02 '22 at 15:45

Constraint not working as desired for my INSERT?

1 Answers1