postgresql: there is no unique constraint matching given keys for referenced table

Question

I don't know how to resolve my situation:

create Table VIPUsers(
  id int primary key,
  parent int references Parent(id)
);

create Table Parent(
  id int not null,
  childType int not null,/*indicates ChildA or ChildB*/
  unique (id,childType)
);
create index on Parent(id);

create Table ChildA(
  parent int references Parent(id),
  propertyA int
);

create Table ChildB(
  parent int references Parent(id),
  propertyB date
);

There is M to N relationship between tables VIPUsers and ChildA, ChildB.
Table Parent is a junction table containing information whether VIPUsers uses instances of ChildA and/or instances of ChildB

I need to add rows:

insert into Parent (id,subtype) values (1,1)
insert into Parent (id,subtype) values (1,2)

This gave me an error 'duplicate primary key' so I deleted the primary key and I'd use an index instead, just as it is shown above.

But now I can't create tables VIPUsers, ChildA, ChildB as I get the error

'there is no unique constraint matching given keys for referenced table ...' 

I know it is because VIPUsers would not reference a unique row. But that's what I need. I need a join where for each parent I get one or more childTypes. For example:

  VIPUsers ParentId   childType
    1         1          1
    1         1          2

If I make Parent.id a primary key, there will be only one childType for each user. If I remove primary key, DBMS starts complaining about referencing a non unique column.

My goal is to then cross join the above example table with ChildA or ChildB on ParentId to get table like this:

 VIPUsers  ParentId   childType  ChildA.propertyA  ChildB.propertyB 
    1         1          1             50                 null
    1         1          2            null              2/2/2017

And based on the childType I'm able to tell what kind of information this row represents.

I don't like putting childType into each Child table because it would allow for nonsense values like ChildA being of type ChildB and vice versa.

What are the usual ways of going about this?

Answer 1

After some clarification (in comments and chat), it seems that:

• ChildA and ChildB are subtypes.
• a VIPUser has a many-to-many relationship with all Child entities ("VIPUser "uses" Child").
• there is a many-to-many relationship between VIPUser and ChildType (stored in Parent), essentially what types of Child a `VIPuser can use ("VIPUser "can use" a child of ChildType").

Then the relationships between entities can be shown in the diagram
(I renamed Parent to VIPCanUse):

               ChildType
VIPUser        /      \
      \       /        \
       \     /          \
      VIPCanUse          \
           \           Child
            \         /    |
             \       /     ------------
              \     /        |        | 
               \   /       ChildA   ChildB
              VipUses

Answer 2

I have a wrong schema and the answer is, I should pay a very close attention to what every reference mean, and if this really supports all of the requirements I need.