CODEWITHSUNDEEP
sqlpostgresqlpostgresql-9.4
chungtinhlakho
chungtinhlakho

Reputation: 930

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,

Upvotes: 0

Views: 112

Answers (1)

Robert Columbia
Robert Columbia

Reputation: 6418

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.

Upvotes: 1

Related Questions