PostgreSQL如何约束同一父级不能有两个同年龄受宠子女

Got it, let's break down how to solve this problem. The core requirement is: for the same parent, you can't have two children with the same age who are both marked as appreciated = true. Regular unique constraints won't work here because they'd restrict all rows, even those where appreciated is false. Instead, we'll use a PostgreSQL-specific feature called a partial unique index—it's perfect for enforcing uniqueness only when certain conditions are met.

Step-by-Step Solution #

1. Create the Partial Unique Index #

Run this SQL command to add the constraint:

CREATE UNIQUE INDEX idx_child_parent_age_appreciated
ON child (parent, age)
WHERE appreciated = true;

What This Does #

• This index only applies to rows where appreciated is true.
• For those rows, it ensures that the combination of parent (the parent's ID) and age is completely unique.
• Rows where appreciated is false are ignored by this index—so you can have multiple children with the same age under the same parent, as long as they're not marked as appreciated.

Test the Constraint #

Let's verify this works with some examples:

First, add a parent:

INSERT INTO parent (name) VALUES ('John Doe');
-- Let's assume this returns a parent ID of 1

Now insert valid records:

-- Appreciated child, age 10
INSERT INTO child (parent, name, age, appreciated) VALUES (1, 'Alice', 10, true);

-- Another child with same age, but not appreciated (allowed)
INSERT INTO child (parent, name, age, appreciated) VALUES (1, 'Bob', 10, false);

-- Appreciated child with different age (allowed)
INSERT INTO child (parent, name, age, appreciated) VALUES (1, 'Charlie', 12, true);

Now try inserting an invalid record (same parent, same age, appreciated = true):

INSERT INTO child (parent, name, age, appreciated) VALUES (1, 'Dave', 10, true);

You'll get an error like this:

ERROR: duplicate key value violates unique constraint "idx_child_parent_age_appreciated"
DETAIL: Key (parent, age)=(1, 10) already exists.

Exactly what we want—this blocks the duplicate appreciated child with the same age under the same parent.

Why This Is the Best Approach #

PostgreSQL doesn't support "conditional unique constraints" natively in the SQL standard, but partial unique indexes are the idiomatic way to achieve this behavior. They're efficient (since they only index the rows that matter) and clearly express the business rule in your schema.

内容的提问来源于stack exchange，提问作者Gershom Maes