CODEWITHSUNDEEP
postgresqlforeign-keys
Bruno
Bruno

Reputation: 153

Is possible to create a foreign key without a primary key?

I am creating two tables and I want to put a foreign key on the column customer_email knowing is not the primary key.

So is possible to create a foreign key without a primary key? And how I use DATE()?

Here is my table customer and order:

CREATE TABLE customer(
customer_id INTEGER PRIMARY KEY NOT NULL,
customer_email VARCHAR(225)
customer_name VARCHAR(225)
customer_last_name VARCHAR(225)
);

CREATE TABLE order(
order_id INTEGER PRIMARY KEY NOT NULL
customer_email  VARCHAR(225) REFERENCES customer(customer_email)
product_1 DECIMAL(10,2)
product_2 DECIMAL(10,2)
product_3 DECIMAL(10,2)
product_4 DECIAML(10,2)
product_5 DECIAML(10,2)
order_date DATE()
);

Upvotes: 0

Views: 800

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521179

Postgres' documentation for the creation of foreign keys addresses your question:

Finally, we should mention that a foreign key must reference columns that either are a primary key or form a unique constraint. If the foreign key references a unique constraint, there are some additional possibilities regarding how null values are matched.

So, you certainly can create a foreign key in the order table which references customer_email in the customer table, but there would have to be a unique constraint on the email field. So, the following definition for the customer table should work:

CREATE TABLE customer (
    customer_id INTEGER PRIMARY KEY NOT NULL,
    customer_email VARCHAR(225),
    customer_name VARCHAR(225),
    customer_last_name VARCHAR(225),
    CONSTRAINT email_unique UNIQUE (customer_email)
);

Upvotes: 2

Related Questions