0

Creating a table with Primary Key (customer_id) which matches another PK from another table, and trying to reference that.

    CREATE TABLE customer_leads(
        customer_id SERIAL PRIMARY KEY, 
        first_name VARCHAR(25) NOT NULL,
        last_name VARCHAR(25) NOT NULL,
        email VARCHAR(25) NOT NULL UNIQUE,
        signup_date TIMESTAMP NOT NULL,
        duration TIME NOT NULL,
        PRIMARY KEY(customer_id) REFERENCES customer(customer_id)
        )
Mir
  • 55
  • 1
  • 9
  • 1
    This is a faq. Before considering posting please always google your error message or many clear, concise & precise phrasings of your question/problem/goal, with & without your particular strings/names & site:stackoverflow.com & tags, & read many answers. If you post a question, use one phrasing as title. See [ask] & the voting arrow mouseover texts. PS A PK is unique not null & a FK references a unique. When either happens declare it, if it isn't already implied by another declaration. https://stackoverflow.com/a/34406731/3404097 – philipxy Nov 02 '19 at 04:01

1 Answers1

3

I don't think that this makes sense. It would make sense like this:

CREATE TABLE customer_leads(
    customer_lead_id SERIAL PRIMARY KEY, 
    . . . 
);

CREATE TABLE customers (
    customer_id int PRIMARY KEY, 
    . . . 
    FOREIGN KEY(customer_id) REFERENCES customer_leads(customer_lead_id)
);

This formulation says that every customer starts out as a customer lead. The "customer_id" is assigned at the lead level, and some of these are eventually turned into customers.

This makes sense. The foreign key reference is from the customers table. All customers have valid leads, but not all leads are (necessarily) valid customers.

Your structure is troublesome. Basically, the customer_id is assigned in customer leads -- by the serial column. Then, this is supposed to be a reference to the primary key of another table. But how is that value calculated? The structure doesn't make sense to me.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I see. I don't think I have a clear understanding of what REFERENCES is actually doing or its purpose. In order to have your PK reference another PK, you set that as a Foreign Key as you did with 'customers' table? Also, why did you not have customer_id in SERIAL? Thanks for the help. – Mir Nov 02 '19 at 02:36
  • @Mir . . . Normally, these would be different keys. But the question is specifically about having a primary key also be a foreign key. – Gordon Linoff Nov 02 '19 at 18:57