Constraint uniq external reference for two rows

accounts.sql

-- References:
-- https://stackoverflow.com/a/55223096/4constraint 69463

-- Table Definition ----------------------------------------------

CREATE TABLE accounts (
    id bigint DEFAULT nextval('id_seq'::regclass) PRIMARY KEY,
    natural_person_id bigint,
    legal_entity_id bigint,
    CONSTRAINT chk_only_one_is_not_null CHECK (num_nonnulls(natural_person_id, legal_entity_id) = 1)
);

-- Indices -------------------------------------------------------

CREATE UNIQUE INDEX accounts_pkey ON accounts(id int8_ops);
CREATE UNIQUE INDEX legal_entity_uni_idx ON accounts (legal_entity_id);
CREATE UNIQUE INDEX natural_person_uni_idx ON accounts (natural_person_id);

-- constraint -------------------------------------------------------
ALTER TABLE accounts ADD CONSTRAINT chk_only_one_is_not_null CHECK (num_nonnulls(natural_person_id, legal_entity_id) = 1);

-- Will be inserted -------------------------------------------------------
INSERT INTO accounts (natural_person_id, legal_entity_id) VALUES (1, NULL);
INSERT INTO accounts (natural_person_id, legal_entity_id) VALUES (2, NULL);
INSERT INTO accounts (natural_person_id, legal_entity_id) VALUES (NULL, 1);

-- Will be not inserted -------------------------------------------------------
INSERT INTO accounts (natural_person_id, legal_entity_id) VALUES (NULL, NULL);
INSERT INTO accounts (natural_person_id, legal_entity_id) VALUES (3, 2);

TRUNCATE TABLE accounts;
SELECT * FROM accounts;