What database constraint would help prevent duplicate active addresses for the same recipient?

What database constraint would help prevent duplicate active addresses for the same recipient?

• A. A composite unique index on (recipient_id, normalized_address_key, country) plus a constraint to limit active records
• B. A unique index on (recipient_id, address_id) that updates with changes
• C. A check constraint enforcing that country is not null
• D. No constraints

Answer: (A)

The idea is to prevent more than one active address for a given recipient by tying uniqueness to the fields that define the address, while still allowing history. A composite unique index on recipient_id, normalized_address_key, and country ensures that you cannot have two rows representing the same address for the same recipient in the same country when those rows are considered active. Adding a constraint that applies only to active records (for example, a unique condition where active = true) lets historical, inactive addresses exist without blocking new active ones. In practice, this means you can insert or update rows, but activating a second row for the same recipient and address would be rejected by the index, preserving a single active address per recipient-address combination. The other options don’t enforce this specific rule: a unique index on recipient_id and address_id would not guard against duplicate active addresses across different records, a non-null country check doesn’t address duplicates, and having no constraints provides no protection at all.

The idea is to prevent more than one active address for a given recipient by tying uniqueness to the fields that define the address, while still allowing history. A composite unique index on recipient_id, normalized_address_key, and country ensures that you cannot have two rows representing the same address for the same recipient in the same country when those rows are considered active. Adding a constraint that applies only to active records (for example, a unique condition where active = true) lets historical, inactive addresses exist without blocking new active ones. In practice, this means you can insert or update rows, but activating a second row for the same recipient and address would be rejected by the index, preserving a single active address per recipient-address combination. The other options don’t enforce this specific rule: a unique index on recipient_id and address_id would not guard against duplicate active addresses across different records, a non-null country check doesn’t address duplicates, and having no constraints provides no protection at all.