What constrain to use when trying to have one type of something (ex.bank account) for each ID?-CodePudding

I currently have a ACCOUNTS table with account_id attribute as primary key, cookie_type_id as foreign key, and client_id as a foreign key. When trying to make sure that each client_id has no overlap in cookie_type_id (one client cannot make 2 chocolate cookies), should I be using UNIQUE contrain on foreign key like:

ALTER TABLE ACCOUNTS ADD UNIQUE cookie_type_id ? The problem is if the client_id is different, it's okay to have an overlap in cookie_type_id. In this case, what code should I use?

Thank you in advance

CodePudding user response：

You should also add client_id to the unique constraint to prevent a client to have the same cookie_type_id. in this way:

ALTER TABLE ACCOUNTS ADD UNIQUE unique_index(cookie_type_id, client_id);

CodePudding user response：

Just create a unique constraint on the columns client_id, cookie_type_id