An entitiy with at least one weak entity

Question

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.

Answer 1

As was mentioned in the comments, there could be name duplicates. It's better to have an id column in each table.

Second, adding NOT NULL constraint on the foreign key will enforce the rule that each child can have only one parent

Create Table Parent(
  id int NOT NULL UNIQUE,
  parentName Varchar(255),
  -- Should a parent have an entity named child? -- No.
  -- But a parent can have more than one child
  PRIMARY KEY(id)
);

Create Table Child(
 id int NOT NULL UNIQUE,
 parent_id int NOT NULL, -- NOT NULL - each child has a parent
 name varchar(255),
 PRIMARY KEY(id),
 FOREIGN KEY (parent_id) References Parent(id)
);

There's only one parent_id field, so each child has no more than one parent

Because each child can have only one parent (one-to-many) relationship, the two-table solution works.

When you have many-to-many relationship, you need a relationship table that contains foreign keys to both tables that represent entities in this relationship.

You might also want to add another UNIQUE to (parent_id, name) combination, to prevent one parent have multiple children with the same name. (Though, I have heard that some celebrity named all his sons "George")

You can't really enforce that each parent has at least one child, because then you wouldn't be able to add new parents to your database. Current process is:

1. insert a parent
2. insert that parent's children

You can't insert children first, because the constraints will prevent the insert of a child without a parent already existing.