chungtinhlakho chungtinhlakho - 5 months ago 17
SQL Question

PostgreSQL referencing error

I have a landlord table and a listing table. when creating my listing table, I received this error.

ERROR: there is no unique constraint matching given keys for referenced table "t_property_landlord"
SQL state: 42830

create table if not exists t_listing(
rental_listing_id BIGSERIAL primary key,
landlord_id BIGSERIAL references t_property_landlord(landlord_id),
available varchar(25),
rent numeric,
deposit numeric,
description text,
term varchar(25),
pet boolean,
feature JSON,
post_ts date,
view numeric,
create_ts timestamp default now()
);


Here is the landlord table.

create table if not exists t_property_landlord(
landlord_id BIGSERIAL,
email varchar(100) references t_user(email),
property_id BIGSERIAL references t_property(property_id),
change_ts timestamp default now(),
active boolean default true,
primary key(email,property_id)
);


I tried create create_ts, and reference the landlord_id, making those you unique but still not working. Can someone take a look and let me know what I'm doing wrong? I am using PostgreSQL 9.4

thanks,

Answer

t_property_landlord.landlord_id isn't a unique column. It needs to be for a foreign key to be able to reference it. See here for information about how to declare one: https://www.postgresql.org/docs/8.1/static/ddl-constraints.html

Your t_property_landlord table should look like this:

create table if not exists t_property_landlord(
    landlord_id BIGSERIAL UNIQUE NOT NULL,
    email varchar(100) references t_user(email),
    property_id BIGSERIAL references t_property(property_id),
    change_ts timestamp default now(),
    active boolean default true,
    primary key(email,property_id)
);

In the end, you might want to take another look at your design, as you are trying to make a foreign key reference a column in another table that is not that table's primary key. Usually, foreign keys should reference primary keys.

Comments