CODEWITHSUNDEEP
sqldatabasepostgresqlrelationship
Prome88
Prome88

Reputation: 125

Postgresql foreign key as primary not unique?

So i have a table, that takes a nickname as foreign key and a id to form a primary key:

CREATE TABLE Character(
    Nickname TEXT,
    CONSTRAINT person_pk PRIMARY KEY(Nickname)
);
CREATE TABLE POSTING(
    PostingID BIGINT UNIQUE, 
    Nickname TEXT,
CONSTRAINT posting_fk FOREIGN KEY(Nickname) REFERENCES Person(Nickname),
    CONSTRAINT postings_pk PRIMARY KEY(PostingID, Nickname)
);

So far, so good. However, whenever i try to get now the primary key from posting, it tells me that: " there is no unique constraint matching given keys for referenced table "information"".

CREATE TABLE INFORMATION(
    InformationID BIGINT,
    PostingID BIGINT, 
    CONSTRAINT informations_fk FOREIGN KEY(PostingID) REFERENCES POSTING(PostingID),
    CONSTRAINT informations_pk PRIMARY KEY(InformationID, PostingID)
);


CREATE TABLE DATA(
    InformationID BIGINT,
    Link TEXT NOT NULL,
    CONSTRAINT data_fk FOREIGN KEY(InformationID) REFERENCES INFORMATION(InformationID),
    CONSTRAINT datas_pk PRIMARY KEY(InformationID)
);

However, if i change in the table Information the InformationID to unique, the error disappears. Is that the correct way of doing things?

CREATE TABLE INFORMATION(
    InformationID BIGINT UNIQUE,
    PostingID BIGINT,
    CONSTRAINT informations_fk FOREIGN KEY(PostingID) REFERENCES POSTING(PostingID),
    CONSTRAINT informations_pk PRIMARY KEY(InformationID, PostingID)
);

Thanks in advance everyone!

Upvotes: 1

Views: 817

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247535

Either the primary key for information should be only informationid, or data must include postingid and the foreign key to information is defined on both columns.

Hard to say without knowing your data, but I suspect the former solution to be the correct one.

Upvotes: 1

Related Questions