即使有序列，客戶ID也為空

這是我的創建表，我使用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', '[email protected]', 'mass2000', 'Passw0rd', ' 4367712333213', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Martin' , 'Mahmody', '[email protected]', 'mahmo00', 'MAhmoistStarK1', ' 4367700992112', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Sebastian',  'Dorner', '[email protected]', 'bastian13', 'MyPasswordIsEasy', ' 436993213213', 111);

因此，當我運行它時，它說 Customer_ID 是NULL，這不應該是，因為我每次插入新用戶/客戶時都會增加序列。

uj5u.com熱心網友回復：

您沒有在 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','[email protected]','mass2000','Passw0rd',' 4367712333213',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','[email protected]','mahmo00','MAhmoistStarK1',' 4367700992112',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','[email protected]','bastian13','MyPasswordIsEasy',' 436993213213',111);

標籤：sql PostgreSQL

上一篇：轉換字串以在Java中的SQLWHEREIN子句中傳遞多個值

下一篇：使用連接查詢JSONB列以通過參考外部查詢的子查詢進行過濾