在SQL中使用字符串和整数生成约束

Question

我是初学者。我在PostgreSQL上创建了一个查询。我想用字符串和整数进行约束。它已成功运行，但当我插入数据时，它给了我一个错误

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')

信息

错误：关系“客户”的新行违反检查约束“cek_id_customer”详细信息：失败的行包含（CU001、Sayaa、男性）。SQL状态：23514

Answer 1

要强制执行所需的匹配模式，可以在约束中使用正则表达式匹配规则，如id_customer~“^CU[0-9]*$”。

例如：

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!

请参阅DB Fiddle上的运行示例。