Unique constraint with foreign key

Question

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.

Answer 1

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.