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?

• Sahap Asci: Did you know that PostgreSQL has inheritance feature? Check Inheritance section.
• Daniel Katz: I knew. I didn't want to use it because of the second-last sentence. I think it would do what I don't want.
• Daniel Katz: @SahapAsci But maybe what you're saying is the only solution.
• ypercubeᵀᴹ: What is the relationship between Parent and ChildA (and ChildB)? One to one? One to many?
• ypercubeᵀᴹ: What is the relationship between ChildA and ChildB? What is the relationship between VIPUsers and ChildA (and ChildB)? The question is not clear at all about what exactly you want to model. No wonder that the attempted solutions have issues

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.