Allowing duplicates in certain circumstances

[DanMurphy]

Hello

An idea for my database, but I have an issue I am unsure how to handle.

I have a picture of a sample table attached. CommandmentID is the primary key, CommandmentSet is a foreign key, with the other two fields not having an index.

Is it possible to set the CommandmentNumber field to only allow a number where that number is not present in CommandmentSet? So, it would allow a 4th comandment for Bible but not for Access? Or should the layout of the tables be changed?

Its going to be for a small database that will keep track of terms and conditions for various insurance policy documents.

Thanks in advance.

 

[Endre]

I am not certain of your need for a CommandmentID Primary Key. Why not just combine the CommandmentNumber AND the CommandmentSet and use them combined together as your Primary Key. This will allow a 4th commandment for Bible, but not for Access - exactly as you require. If you still need your autonumber, then keep it, but not as your Primary Key.