PostgreSQL:在联接表上创建唯一约束(多表常量)

Question

想象一下，我有这个SQL:

SELECT
directed_events.id as event_id,
directed_events.event_type as event_type,
issuers.referable_id as issuer_id,
issuers.referable_type as issuer_type,
targets.referable_id as target_id,
targets.referable_type as target_type
FROM
events AS directed_events
INNER JOIN "event_refs" "issuers" 
ON "directed_events"."id" = "issuers"."event_id"
AND "issuers"."connection_type" = 'issuer'
INNER JOIN "event_refs" "targets" 
ON "directed_events"."id" = "targets"."event_id"
AND "targets"."connection_type" = 'target'

我想确保对于一组值(event_type、issuer_id、target_id(，数据库内容是唯一的，因此必须拒绝重复插入。

当然，我可以在应用程序级别完成。但数据库级别的约束是更可靠的方法。

Answer 1

您可以在上使用concat+distinct

SELECT DISTINCT ON (conc_id) *
FROM (
SELECT
directed_events.id as event_id,
directed_events.event_type as event_type,
issuers.referable_id as issuer_id,
issuers.referable_type as issuer_type,
targets.referable_id as target_id,
targets.referable_type as target_type,
-- concat columns to have only one column for unique check
CONCAT(directed_events.event_type::text, '-', issuers.referable_id::text, '-', targets.referable_id::text) as conc_id
FROM
events AS directed_events
INNER JOIN "event_refs" "issuers" 
ON "directed_events"."id" = "issuers"."event_id"
AND "issuers"."connection_type" = 'issuer'
INNER JOIN "event_refs" "targets" 
ON "directed_events"."id" = "targets"."event_id"
AND "targets"."connection_type" = 'target'
) as data
ORDER BY conc_id ASC