Numeric limit on composite key field

Question

My RelationshipCodeLookup table uses as a foreign key the primary key field of the AgentTransmission table. There can be only 5 records in the RelationshipCodeLookup table with the same AgentTransmission primary key.

Is it possible to create a composite key on the RelationshipCodeLookup which includes the foreign key from AgentTransmission and a separate int field that only goes up to 5? For instance, a complete group of records for one agent would be:

FK   CK
132   1
132   2
132   3
132   4
132   5

If another record was attempted to be added with the FK of 132, some kind of constraint on the table would be violated and the record unable to be added.

Is this possible?

Answer 1

If I understand your question correctly, I think a simple check constraint on the CK column would do what you need. The Check constraint requires that values in the CK column are in the specified range ( 1 thru 5 ).

CREATE TABLE RelationshipCodeLookup(
    FK INT NOT NULL, 
    CK INT NOT NULL,
    CONSTRAINT PK_RelationshipCodeLookup PRIMARY KEY CLUSTERED ( FK, CK )  
);
ALTER TABLE RelationshipCodeLookup WITH CHECK ADD CONSTRAINT CK_RelationshipCodeLookup CHECK (CK>=1 AND CK<=5);
ALTER TABLE RelationshipCodeLookup CHECK CONSTRAINT CK_RelationshipCodeLookup;