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?
Parent
andChildA
(and ChildB)? One to one? One to many?ChildA
andChildB
? What is the relationship betweenVIPUsers
andChildA
(and ChildB)? The question is not clear at all about what exactly you want to model. No wonder that the attempted solutions have issues