multi valued attribute as primary key

I am designing a database system for my current project and got this situation.
I am having a functional dependency as


D -> E

Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination. And Unique combination of D will define unique value of E. So i am interested in knowing what is the best way of achieving this requirement.

Just to be clear, you can have multi-value primary keys. But PRIMARY KEY means all values in the composite key are NOT NULL.

CREATE TABLE d ( i int, j int, k int, PRIMARY KEY (i,j,k) );

INSERT INTO d (i,j,k) VALUES (1,2,null);
ERROR:  null value in column "k" violates not-null constraint
DETAIL:  Failing row contains (1, 2, null).

So, you must be unique on (i,j,k). That makes sense. This can provide a linear map to E in your example


Here D is multi valued attribute. D can be(i,j,k) or (i,j) or any other combination.

That's where you've lost me. That doesn't make sense. If they're all NOT NULL the only combination that matters is (i,j,k)

If you want them such that reordering is also not allowed, for instance. Such that these are rejected..

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
INSERT INTO d (i,j,k) VALUES (1,2,3), (2,1,3);

We can do that too using an EXCLUDE constraint and sort with an array.

CREATE TABLE d (
  i int,
  j int,
  k int,
  PRIMARY KEY (i,j,k),
  EXCLUDE (sort(ARRAY[i,j,k]) WITH =)
);

Now we get our error.

INSERT INTO d (i,j,k) VALUES (1,2,3), (3,2,1);
ERROR:  conflicting key value violates exclusion constraint "d_sort_excl"
DETAIL:  Key (sort(ARRAY[i, j, k]))=({1,2,3}) conflicts with existing key (sort(ARRAY[i, j, k]))=({1,2,3}).

StackExchange Database Administrators Q#120147, answer score: 2