So i am implementing a system that has PIN (Personal identification number) codes. The PIN is a simple combination of a few letters and some numbers. The PIN could be something like AX7500.

Now, a entity in my database can have many PIN codes, and they must all be unique, but ONLY to that entity ID.

So two different entities can have the same PIN code.

How should i implement this? Should i implement this on the application level, or is it possible to do in the database with some constraints?

Im using Postgres 9.5


You can do this with a simple composite UNIQUE constraint: UNIQUE (entity_id, pin_code). Example table:

CREATE TABLE pin_codes
  ( entity_id  int NOT NULL,
    pin_code   text NOT NULL,
    FOREIGN KEY (entity_id)
        REFERENCES entities (entity_id),
    UNIQUE (entity_id, pin_code) 
  ) ;
| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.