I have the following design:
There is a list of parents in my database. A parent should have at least one child, and a child must belong to a parent (forget about the husband/wife relations). (a child is a weak entity)
(No need to add rules for deletion/ update)
How can I achieve this?
I write SQL but I have the problems:
Create Table Parent( parentName Varchar(255), -- Should a parent have an entity named child? -- But a parent can have more than one child PRIMARY KEY(parentName) ); Create Table Child( parentName varchar(255), name varchar(255), PRIMARY KEY(parentName, name), FOREIGN KEY (parentName) References Parent(parentName));
I'm assuming that I have the following error:
- A parent should not exist without a child. However, I could not manage to maintain this.
As an example
parent 1, child1
parent 1, child2
parent 2, child2 --Not allowed!
parent 3, null -- Not allowed!
null, child 4 -- Not allowed!
Also, I need a child for other relations as well. So, in my opinion, combining child entity with a relation in a table is not a good solution for me.