sql - 客户 ID 为空，即使有序列

Question

这是我的创建表，我正在使用INT数据类型Customer ID，Restaurant ID和Owner ID。

DROP TABLE RESERVATION;
DROP TABLE RESTAURANT;
DROP TABLE CUSTOMER;
DROP TABLE RESTAURANTOwner;

CREATE EXTENSION IF NOT EXISTS citext;

CREATE TABLE CUSTOMER 
(
    CUSTOMER_ID INT primary key NOT NULL,
    CUSTOMER_FIRSTNAME VARCHAR(30),
    CUSTOMER_SECONDNAME VARCHAR(30),
    CUSTOMER_EMAIL CITEXT, 
    CUSTOMER_USERNAME VARCHAR(80), 
    CUSTOMER_PASSWORD VARCHAR(80), 
    CUSTOMER_PHONE VARCHAR(20),
    CUSTOMER_SALT INT NOT NULL
);

CREATE TABLE RESTAURANTOwner 
(
    OWNER_ID INT primary key NOT NULL,
    OWNER_FIRSTNAME VARCHAR(30),
    OWNER_SECONDNAME VARCHAR(30),
    OWNER_EMAIL CITEXT, 
    OWNER_USERNAME VARCHAR(80), 
    OWNER_PASSWORD VARCHAR(80), 
    OWNER_PHONE VARCHAR(20),
    OWNER_SALT INT NOT NULL
);

CREATE TABLE RESTAURANT 
(
    RESTAURANT_ID INT primary key NOT NULL,
    OWNER_ID INT NOT NULL,
    RESTAURANT_NAME VARCHAR(40),
    RESTAURANT_LAYOUT VARCHAR(255),

    CONSTRAINT FK_OWNER_ID 
        FOREIGN KEY (OWNER_ID) 
            REFERENCES RESTAURANTOwner(OWNER_ID)
);

CREATE TABLE RESERVATION 
(
    RESTAURANT_ID INT NOT NULL,
    CUSTOMER_ID INT NOT NULL,
    RESERVATION_TIME Time,
    RESERVATION_DATE DATE,

    PRIMARY KEY (RESTAURANT_ID, CUSTOMER_ID, RESERVATION_TIME, RESERVATION_DATE),
    CONSTRAINT FK_CUSTOMER_ID 
        FOREIGN key (CUSTOMER_ID) 
            REFERENCES CUSTOMER(CUSTOMER_ID),
    CONSTRAINT FK_RESTAURANT_ID 
        FOREIGN KEY (RESTAURANT_ID) 
            REFERENCES RESTAURANT(RESTAURANT_ID)
);

这是序列和插入。如您所见，它们从 1 开始并以 1 递增，这意味着客户 ID 不能为 NULL。

  CREATE SEQUENCE IF NOT EXISTS public.CUSTOMER_ID
            START WITH 1
            INCREMENT BY 1
            NO MINVALUE
            NO MAXVALUE
            CACHE 1;
            
  CREATE SEQUENCE IF NOT EXISTS public.OWNER_ID
            START WITH 1
            INCREMENT BY 1
            NO MINVALUE
            NO MAXVALUE
            CACHE 1;
            
  CREATE SEQUENCE IF NOT EXISTS public.RESTAURANT_ID
            START WITH 1
            INCREMENT BY 1
            NO MINVALUE
            NO MAXVALUE
            CACHE 1;

INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Asik', 'Biho', '[e-mail]', 'mass2000', 'Passw0rd', '[phone]', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Martin' , 'Mahmody', '[e-mail]', 'mahmo00', 'MAhmoistStarK1', '[phone]', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Sebastian',  'Dorner', '[e-mail]', 'bastian13', 'MyPasswordIsEasy', '[phone]', 111);

所以当我运行它时，它说 Customer_ID 是NULL，这不应该是因为我的序列每次插入新用户/客户时都会增加。

标签： sqlpostgresql

Answer 1

您没有在 Insert 语句中的任何地方使用序列。您需要像下面这样使用它们 -

INSERT INTO CUSTOMER(CUSTOMER_ID,CUSTOMER_FIRSTNAME,CUSTOMER_SECONDNAME,CUSTOMER_EMAIL,CUSTOMER_USERNAME,CUSTOMER_PASSWORD,CUSTOMER_PHONE,CUSTOMER_SALT)
VALUES (NEXTVAL('CUSTOMER_ID'),'Asik','Biho','[e-mail]','mass2000','Passw0rd','[phone]',32131);
INSERT INTO CUSTOMER(CUSTOMER_ID,CUSTOMER_FIRSTNAME,CUSTOMER_SECONDNAME,CUSTOMER_EMAIL,CUSTOMER_USERNAME,CUSTOMER_PASSWORD,CUSTOMER_PHONE,CUSTOMER_SALT)
VALUES (NEXTVAL('CUSTOMER_ID'),'Martin' ,'Mahmody','[e-mail]','mahmo00','MAhmoistStarK1','[phone]',32131);
INSERT INTO CUSTOMER(CUSTOMER_ID,CUSTOMER_FIRSTNAME,CUSTOMER_SECONDNAME,CUSTOMER_EMAIL,CUSTOMER_USERNAME,CUSTOMER_PASSWORD,CUSTOMER_PHONE,CUSTOMER_SALT)
VALUES (NEXTVAL('CUSTOMER_ID'),'Sebastian', 'Dorner','[e-mail]','bastian13','MyPasswordIsEasy','[phone]',111);