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
Parent is a junction table containing information whether
VIPUsers uses instances of
ChildA and/or instances of
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
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?