1

I'm creating an IDs table in PostgreSQL to represent the relationship between multiple foreign IDs - call them a, b and c.

I happen to know that the foreign IDs have one-to-many relationships between them:

a 1..* b b 1..* c

I'd like to represent them as a single table (see below), but enforce the one-to-many invariant between a & b, & between b and c.

Is that possible?

Examples: The following insertions would be illegal:

pk a b c reason
p15 a1 b11 c111 c111 already exists
p16 a1 b21 c213 b21 already exists for a2
p17 a2 b11 c214 b11 already exists for a1

Given the following legal table:

pk a b c
p1 a1 b11 c111
p2 a1 b11 c112
p3 a1 b12 c121
p4 a1 b12 c122
p5 a2 b21 c211
p6 a2 b21 c212
p7 a2 b22 c221
p8 a2 b22 c222
p9 null b31 c311
p10 a3 null c312
p11 a3 b31 null
p12 null null c314
p13 null b31 null
p14 a3 null null
4
  • I'm not sure exactly what you're doing, but you're probably going about it the wrong way, given the presence of as many NULLs and a "primary key" that isn't doing anything other than saying "I'm row 15!". Can you provide some context around exactly what data you're working with and what you're trying to do with it? – bbaird Mar 5 at 16:02
  • I'm associating our own ID for an entity (the pk in this table) with multiple foreign IDs that we are correlating together. We receive the foreign IDs in a haphazard manner - a message may contain one or more of them depending on which system it comes from, and so we have to do complicated logic to correlate them. – Robert Elliot Mar 5 at 16:14
  • Gotcha. Trigger/procedure logic is likely your only option. – bbaird Mar 5 at 16:50
  • This might be simpler, yet. Please disclose your version of Postgres and more about the use case. INSERT only? Can there be DELETE or UPDATE? Roughly how many distinct a, b and c? – Erwin Brandstetter Mar 5 at 20:56
2

This has to be done by TRIGGER, because it is too complex for CHEKCconstraint

For updating you need a similar trigger/function

CREATE TABLE tabl1
    ("pk" varchar(5), "a" varchar(6), "b" varchar(6), "c" varchar(6))
;
    
INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p1', 'a1', 'b11', 'c111'),
    ('p2', 'a1', 'b11', 'c112'),
    ('p3', 'a1', 'b12', 'c121'),
    ('p4', 'a1', 'b12', 'c122'),
    ('p5', 'a2', 'b21', 'c211'),
    ('p6', 'a2', 'b21', 'c212'),
    ('p7', 'a2', 'b22', 'c221'),
    ('p8', 'a2', 'b22', 'c222'),
    ('p9', NULL, 'b31', 'c311'),
    ('p10', 'a3', NULL, 'c312'),
    ('p11', 'a3', 'b31', NULL),
    ('p12', NULL, NULL, 'c314'),
    ('p13', NULL, 'b31', NULL),
    ('p14', 'a3', NULL, NULL)
;
CREATE OR REPLACE FUNCTION check_insert()
RETURNS TRIGGER AS $$
   BEGIN
   IF EXISTS(SELECT 1 FROM tabl1 WHERE "c" = NEW."c" AND "b" = NEW."b" ) then
       raise EXCEPTION 'The column %  already  with %',NEW."c",NEW."b";
   ELSIF EXISTS(SELECT 1 FROM tabl1 WHERE "a" <> NEW."a" AND "b" = NEW."b" ) then
           raise EXCEPTION 'The column %  already  has already a column',NEW."b";
   END IF;
   RETURN NEW;
   END;
$$ LANGUAGE plpgsql;
CREATE  TRIGGER trigger_name BEFORE
 INSERT
ON tabl1
FOR EACH ROW
EXECUTE PROCEDURE check_insert();
INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
    ('p15', 'a1', 'b11', 'c111')
ERROR:  The column c111  already  with b11

CONTEXT: PL/pgSQL function check_insert() line 4 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p16' , 'a1'   , 'b21'  , 'c213' )
ERROR:  The column b21  already  has already a column

CONTEXT: PL/pgSQL function check_insert() line 6 at RAISE

INSERT INTO tabl1
    ("pk", "a", "b", "c")
VALUES
('p17',   'a2',   'b11'   ,'c214')
ERROR:  The column b11  already  has already a column

CONTEXT: PL/pgSQL function check_insert() line 6 at RAISE

SELECT * FROM tabl1
pk  | a    | b    | c   
:-- | :--- | :--- | :---
p1  | a1   | b11  | c111
p2  | a1   | b11  | c112
p3  | a1   | b12  | c121
p4  | a1   | b12  | c122
p5  | a2   | b21  | c211
p6  | a2   | b21  | c212
p7  | a2   | b22  | c221
p8  | a2   | b22  | c222
p9  | null | b31  | c311
p10 | a3   | null | c312
p11 | a3   | b31  | null
p12 | null | null | c314
p13 | null | b31  | null
p14 | a3   | null | null

db<>fiddle here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.