Customer ID null even though there is a sequence

Question

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', 'testmass@gmail.com', 'mass2000', 'Passw0rd', '+4367712333213', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Martin' , 'Mahmody', 'mahmodysecurity@lego.tr', 'mahmo00', 'MAhmoistStarK1', '+4367700992112', 32131);
INSERT INTO CUSTOMER (CUSTOMER_FIRSTNAME, CUSTOMER_SECONDNAME, CUSTOMER_EMAIL, CUSTOMER_USERNAME, CUSTOMER_PASSWORD, CUSTOMER_PHONE, CUSTOMER_SALT)
VALUES ('Sebastian',  'Dorner', 'Sebastian@dorner.at', '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.

You are not using sequence anywhere in your Insert statements. — Ankit Bajpai, Oct 20 '21 at 08:06
Does this answer your question? [Adding 'serial' to existing column in Postgres](https://stackoverflow.com/questions/9490014/adding-serial-to-existing-column-in-postgres) — Pooya, Oct 20 '21 at 08:07
Those sequences are standalone objects - you haven’t done anything to associate them to columns in any tables. Creating a sequence with the same name as a column doesn’t mean there is any relationship between the two objects — NickW, Oct 20 '21 at 08:08

score 2 · Accepted Answer · answered Oct 20 '21 at 08:10

2

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','testmass@gmail.com','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','mahmodysecurity@lego.tr','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','Sebastian@dorner.at','bastian13','MyPasswordIsEasy','+436993213213',111);

answered Oct 20 '21 at 08:10

Ankit Bajpai

13,128
4
25
40

May I ask you one more question, if we e.g. register a user using a web application which is connected to this db. Are we going to use the sequence automatically or do I need to invoke it somehow? – Kil Gore Oct 20 '21 at 08:15
You need to use the sequence name like above in your insert statement, and rest values will come from web application. – Ankit Bajpai Oct 20 '21 at 08:16
Is there any sample so I could see how it looks, because I'm not sure how to use the sequence if the values come from web application, because I cannot fetch them ? or I might be wrong – Kil Gore Oct 20 '21 at 08:24
Please don't do this. If your table needs to use a sequence, then associate the sequence with that field. Don't put sequence values manually. – 404 Oct 20 '21 at 08:51
@404 Would you suggest using SERIAL as datetype or ? – Kil Gore Oct 20 '21 at 09:07
@KilGore What you've done in the table creation statement is fine, but you need to change this: `CUSTOMER_ID INT primary key NOT NULL` to `CUSTOMER_ID INT primary key NOT NULL DEFAULT NEXTVAL('public.CUSTOMER_ID')` in order to get the sequence values automatically. Or if you can't recreate your table, then just alter it: `ALTER TABLE customer ALTER COLUMN customer_id SET DEFAULT NEXTVAL('public.CUSTOMER_ID')` – 404 Oct 20 '21 at 09:13
@404 Thanks, if I delete the table and then insert the inserts, the id doens't start by 1 it continuous where it stopped. Do you know why ? – Kil Gore Oct 20 '21 at 09:53
@KilGore Your sequence is created separately. If you recreate your table, the sequence doesn't get recreated, it remains and calling `nextval` will just get whatever the next value is. If you want the sequence to restart, then either recreate the sequence as well, or use `serial` to have that done automatically. – 404 Oct 20 '21 at 10:58

Customer ID null even though there is a sequence

1 Answers1