Primary Keys (1 Viewer)

I have a table for a multiple parents linked to a child table. I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... I thought about making Primary/Secondary/Other a primary key. But then I can only have 1 other. I would have to make a finite number of parents that could be entered and I want an infinite number.... Any suggestions? My end goal is to have a report that only has a primary and second parent on it, but the rest of the parents still exist in the table...

*Child_ID
Parent_First_Name
*Primary/Secondary/Other

what tables do you have?

you only really need one table for children AND parents, you know, to do this.
You may need a second table for parenthood, but you may be able to do this equally well with just 3 extra fields in the table

one for motherID, one for fatherID, (and one for main parent if you like - I mean a mother/father main parent indicator of some sort).

all this assumes a child can only have 1 mother and 1 father! if you need to track guardians, carers, adoptive parents as well as natural parents it's a bit trickier.


a child's parents are easy
a parent's children are those children referenced by the motherID and/or fatherID.


[edit. actually you OUGHT to do it the way I suggested. Parenthood is a naturally recursive relationship. A grandparent is just a parent's parent. A family tree can easily be traced with such a table. It may be relevant at some point]


sort of these fields

person table

id
person name
motherID (id of another record in this table)
fatherID (id of another record in this table)
maincarerflag
other info you need

You can enforce this kind of rule using a CHECK constraint rather than a uniqueness constraint. For example:

ALTER TABLE YourTable
ADD CONSTRAINT primary_secondary_parents_must_be_unique
CHECK (NOT EXISTS
(SELECT 1 FROM YourTable WHERE Type <> 'Other'
GROUP BY ChildId, Type HAVING COUNT(*)>1));

Note: unfortunately you can't execute this SQL in a query window, you have to use the VBA Execute method instead.

I believe you need a table for tblChildren.

This table has a field linked to a second table tblParents.

This is a simple one to many relationship. You can have one or more parents.

Perhaps you are looking for something more advanced but this is what I believe you need based upon the information you have given.

I have no idea where Buton Moon is coming from. He may have got his posts mixed up.

The question actually seemed pretty clear to me but I am the only person who has answered it:

I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other... Any suggestions?

Click to expand...

I need to figure out a way to only allow 1 parent to be coded as primary, 1 as secondary, and then the rest as other

Click to expand...

You can't have the one field as Primary part of the time. It is either Primary or it is not.

Suggest that this field not be used as the Primary. I am sure you have some good reasons behind this. Could you please explain where you are wanting to go and why.

If you can't find a different field that is suitable for use as Primary then you should consider using Auto Number.

If you understand Button's code then you could use it. Otherwise you can make it an index and not allow duplicates. This is done in the properties of the table.

One final point is that if you use more than one field for a Parent then this is a violation of Normalisation.

I don't think he means primary as in PK.

He just wants that given a child with two parents, he wants to be able to designate one (and only one) of the parents as having "primary care responsibility". That's what I think he is after.

If you understand Button's code then you could use it. Otherwise you can make it an index and not allow duplicates.

Click to expand...

The OP explained in his initial post that a uniqueness constraint (whether primary key or unique index) doesn't help because he wants to allow more than one parent of type "Other".

if you use more than one field for a Parent then this is a violation of Normalisation

Click to expand...

That's not true. Having two (non-nullable) parent attributes is not a violation of any Normal Form. I don't think that is what is being proposed here in any case.

gemma-the-husky said:

I don't think he means primary as in PK.

He just wants that given a child with two parents, he wants to be able to designate one (and only one) of the parents as having "primary care responsibility". That's what I think he is after.

Click to expand...

Thanks Dave.

My advise would still stand would it not. One Field for all parents just add a descriptor Field as Mother, Father etc.