2

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.

  • 2
    Apart from your questions, what if a parent has the same name? It will result in a PK error. Also, what if parent and child have the same name? You would get a constraint error on the PK on child. An identity int column could fix that. Are you using sql-server or mysql? – Randi Vertongen Mar 13 at 10:10
  • 1
    I have simplified the design. Those questions and errors are not the main issues of my problem. I have unique id's and I will create a table with ids. This design is just a simplified illustration of my database. – q851484 Mar 13 at 10:13
  • Check out Joe Celko and Trees in SQL - that might be of help! Upvote for a very good first question! p.s. welcome to the forum - but please, try and choose a nicer handle! :-) – Vérace Mar 13 at 10:21
  • 1
    I didn't mean to offend anyone :) I was just explaining my problem in details – q851484 Mar 13 at 10:25
  • I think it's enough make Child.name UNIQUE. NULLs not allowed because all fields are members of PKs. – Akina Mar 13 at 11:05
0

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.

  • How does adding IDs solve the UNIQUEness problem/constraint? – ypercubeᵀᴹ Mar 14 at 15:57
  • It helps with the problem of multiple parents having the same name – Granny Aching Mar 14 at 15:59
  • Oh ok. But I don't see such a problem discussed in the question (only some mention in comments). The question clearly states PRIMARY KEY(parentName), so you can assume for your answer that this constraints holds. – ypercubeᵀᴹ Mar 14 at 16:05
  • Anothrer issue with your answer: "-- UNIQUE -each child has no more than one parent". That UNIQUE enforces that each parent has no more than 1 child, not what you claim. – ypercubeᵀᴹ Mar 14 at 16:07
  • Yes, constraint holds, but ... what if a parent has the same name? It will result in a PK error – Granny Aching Mar 14 at 16:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.