CODEWITHSUNDEEP
sqldatabasepostgresqlschema
Ollie
Ollie

Reputation: 664

join table referencing non unique foreign key

I have the following tables in a database. phrases allows duplicate phrases to be stored.

CREATE TABLE phrases (
    id INTEGER PRIMARY KEY,
    phrase VARCHAR(1000),
    creation_date TIMESTAMP WITH TIME ZONE NOT NULL,
);

CREATE TABLE intents (
    id INTEGER PRIMARY KEY,
    name VARCHAR(10) UNIQUE
);

I would like to create a join table between phrases and intents. I want to enforce that:

The following fails because the foreign key in phrases is not unique.

CREATE TABLE phrases_intents (
    phrase VARCHAR(1000) REFERENCES phrases (phrase),
    intent VARCHAR(10) REFERENCES intents (name),
    PRIMARY KEY (phrase),
);

What is best practice to allow this functionality?

Upvotes: 0

Views: 291

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270371

Modify the phrases table to have a unique key for foreign key relationships. Perhaps:

CREATE TABLE phrases (
    id INTEGER PRIMARY KEY,
    phrase VARCHAR(1000),
    creation_date TIMESTAMP WITH TIME ZONE NOT NULL,
    unique (phrase, id)
);

Then use this key in phrase_intents:

CREATE TABLE phrase_intents (
    phrase_intents_id serial primary key,
    phrase varchar(1000) not null,
    phrase_id int not null, 
    intent_id int references intents (id),
    foreign key (phrase, phrase_id) references phrases(phrase, id),
    unique (phrase)
);

Your conditions are satisfied:

  1. Each phrase is in the phrases table because of not null and the foreign key reference.
  2. Each distinct phrase appears in this table at most once. This is because of the unique constraint on phrase.

For each phrase to appear exactly once requires populating the table. You will then need triggers on your table to be sure that when a new phrase is created, then a row is inserted.

Upvotes: 2

Related Questions