PostgreSQL表约束：双字段唯一且无关联表引用限制

Let's break down your requirements and implement each one properly in PostgreSQL—no fancy tricks, just solid, maintainable constraints:

1. Enforce Unique Combination of Two Fields #

To guarantee a specific pair of fields in draft_outlooks only appears 0 or 1 times, a UNIQUE constraint is exactly what you need. This natively blocks duplicate combinations, so you’ll never end up with multiple records for the same pair.

When creating the table from scratch: #

CREATE TABLE draft_outlooks (
    id SERIAL PRIMARY KEY,
    user_id INTEGER NOT NULL, -- Replace with your actual first field
    forecast_date DATE NOT NULL, -- Replace with your actual second field
    -- Add your other draft fields here (content, status, etc.)
    CONSTRAINT unique_user_forecast_date UNIQUE (user_id, forecast_date)
);

If the table already exists: #

ALTER TABLE draft_outlooks
ADD CONSTRAINT unique_user_forecast_date UNIQUE (user_id, forecast_date);

Heads up: If either field can be NULL, PostgreSQL treats NULL values as distinct. So if you want to enforce uniqueness only when both fields have values, use a partial unique index instead:

CREATE UNIQUE INDEX idx_unique_non_null_pair ON draft_outlooks (user_id, forecast_date)
WHERE user_id IS NOT NULL AND forecast_date IS NOT NULL;

2. Block Changes When outlook_areas Has References #

To make sure you can’t delete or modify a draft_outlooks record that’s still linked to entries in outlook_areas, you’ll need a foreign key constraint with restriction rules. This prevents orphaned data in the related table.

Example (adding the foreign key to outlook_areas): #

Assuming outlook_areas has a draft_outlook_id column pointing to draft_outlooks.id:

ALTER TABLE outlook_areas
ADD CONSTRAINT fk_outlook_areas_draft
FOREIGN KEY (draft_outlook_id) REFERENCES draft_outlooks(id)
ON DELETE RESTRICT
ON UPDATE RESTRICT;

• ON DELETE RESTRICT: If someone tries to delete a draft that’s referenced in outlook_areas, PostgreSQL throws an error and stops the deletion.
• ON UPDATE RESTRICT: Same logic applies if you try to change the draft’s ID while references exist.

If you’d rather automatically delete related outlook_areas records when a draft is removed, swap RESTRICT for CASCADE—but based on your requirement to "ensure no references exist," RESTRICT is safer to avoid accidental data loss.

Final Result #

With these constraints active:

• Your field pair will only ever have 0 or 1 records in draft_outlooks.
• You can’t delete or modify a draft that’s still tied to area data in outlook_areas.

内容的提问来源于stack exchange，提问作者David A