CODEWITHSUNDEEP
sqlpostgresqlforeign-keysuniquepostgresql-12
Epic Programmer
Epic Programmer

Reputation: 413

Unique constraint with foreign key

I have two Postgres tables with the following columns:

Command

Column Type
id Integer Primary Key
name VARCHAR(32)

Option

Column Type
id Integer Primary Key
name VARCHAR(32)
command_id FOREIGN KEY on COMMAND("id")

I want to add another constraint where the Command name column and the Option command_id columns are tied, such that two commands can share the same name provided they are part of different options. How would I make such a constraint? Would it be better to add no constraint but only allow the backend to make the required checks before entering data?

Edit: I realized that I was overthinking it for my simple use case and that storing a JSON field would be fine enough. However, if the table structure happened to be more complex, then the question would still be valid.

Upvotes: 0

Views: 1787

Answers (1)

eshirvana
eshirvana

Reputation: 24623

if the name from Command table is the same as name column in Option column. then that column in Option table is redundant and you can always fetch the name by FK that you already have (command_id).

but normally you can use composite key for your FK, for example :

 create table Options(
    id int primary key
    , name varchar(32)
    , command_id int
    , foreign key fk_name (name , command_id) references Command(name, id)
);

and of course name and id in command table should be part of candidate key.

Upvotes: 1

Related Questions