Customer ID null even though there is a sequence-CodePudding

Here are my create tables and I'm using INT datatype for Customer ID, Restaurant ID and 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)
);

Here are the sequences and inserts. As you can see the sequences, they start with 1 and increment by 1, which means customer id cannot be 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);

So when I run it, it says that the Customer_ID is NULL, which shouldn't be since I have sequence which increments every time we have insert new user/customer.

CodePudding user response：

You are not using sequence anywhere in your Insert statements. You need to use them like below -

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