Make CONSTRAINT with string and integer in SQL

Question

I am a beginner. I created a query on PostgreSQL. I want to make CONSTRAINT with string and integer. It was successfully running but when I insert the data, it gave me an error

CREATE TABLE customer
(
    id_customer char(5) PRIMARY KEY NOT NULL,
    CONSTRAINT cek_id_customer CHECK ((left(id_customer,2) in ('CU')) 
                                      and  substring(id_customer,3) LIKE '%[0-9]%'), 
    nama_customer varchar(30) NOT NULL,
    gender_customer varchar(15) NOT NULL,
    CONSTRAINT  cek_gender_customer CHECK(gender_customer = 'Male' OR gender_customer = 'Female')
);

INSERT INTO customer 
VALUES ('CU001', 'Sayaa', 'Male')

The message

ERROR: new row for relation "customer" violates check constraint "cek_id_customer" DETAIL: Failing row contains (CU001, Sayaa, Male). SQL state: 23514

Please do not use images for textual information. Add the error message to your question as copy and paste text. — Adrian Klaver
– Adrian Klaver, Commented Oct 28, 2021 at 15:38
What's the error? That like only guarantees a single digit by the way. — shawnt00
– shawnt00, Commented Oct 28, 2021 at 15:39
Are you sure the constraint actually matches what's posted above? — shawnt00
– shawnt00, Commented Oct 28, 2021 at 15:44

The Impaler · Accepted Answer · 2021-10-28 17:30:24Z

1

To enforce the matching pattern you want you can use a regular expression matching rule in the constraint, like id_customer ~ '^CU[0-9]*$'.

For example:

CREATE TABLE customer (
  id_customer char(5) PRIMARY KEY NOT NULL,
  CONSTRAINT cek_id_customer CHECK (id_customer ~ '^CU[0-9]*$'),
  nama_customer varchar(30) NOT NULL,
  gender_customer varchar(15) NOT NULL,
  CONSTRAINT cek_gender_customer CHECK(
    gender_customer = 'Male' OR
    gender_customer = 'Female'
  )
);

INSERT INTO customer (id_customer, nama_customer, gender_customer)
VALUES ('CU001', 'Sayaa', 'Male'); -- succeeds

INSERT INTO customer (id_customer, nama_customer, gender_customer)
VALUES ('CU1X', 'Sayaa', 'Male'); -- fails!

See running example at DB Fiddle.

answered Oct 28, 2021 at 17:30

The Impaler

49.3k10 gold badges50 silver badges90 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Belayer Over a year ago

A somewhat stronger check would be CHECK (id_customer ~ '^CU[0-9]{3}$') . This requires 'CU' followed by exactly 3 digits.

Collectives™ on Stack Overflow

Make CONSTRAINT with string and integer in SQL

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related