PostgreSQL referencing error

Question

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,

landlord_id BIGSERIAL references t_property_landlord(landlord_id), -->> landlord_id BIGINT references t_property_landlord(landlord_id), ( and the same for property_id) — wildplasser
– wildplasser, Commented Jun 16, 2016 at 19:15
bigserial auto-increments. you don't want to use that for your foreign keys. only the parent record that the foreign keys actually point at. — Marc B
– Marc B, Commented Jun 16, 2016 at 19:17

Robert Columbia · Accepted Answer · 2016-06-16 19:16:00Z

1

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.

answered Jun 16, 2016 at 19:16

Robert Columbia

6,45115 gold badges34 silver badges42 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

chungtinhlakho Over a year ago

oh wow, I thought BIGSERIAL is similar to auto number auto number and is unique. thank you so much

Collectives™ on Stack Overflow

PostgreSQL referencing error

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related