How do I handle this referential integrity situation?

[manning_news]

Using 2003. I've got a simple 1:M relationship for employees and
classifications. A classification can be assigned to many employees/an
employee can only have one classification. I created a form for adding
employees/classifications. The classification can be left blank if not
known. I created a 2nd form for modifying employees/classification.
If the classification is left blank, or it's blank out, the 3201
referential integrity error occurs. I understand that there is not a
blank record in the classifications table so that's why the error. But
is there a way to leave the classification blank or blank it out? Do I
have to put a record in the classifications lookup table, like (no
classification)? Thanks for any help or advice.

[pietlinden]

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications. If you set the RI constraint to none, then everything
will be fine.

[Anthony England]

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Using 2003. I've got a simple 1:M relationship for employees and
classifications. A classification can be assigned to many employees/an
employee can only have one classification. I created a form for adding
employees/classifications. The classification can be left blank if not
known. I created a 2nd form for modifying employees/classification.
If the classification is left blank, or it's blank out, the 3201
referential integrity error occurs. I understand that there is not a
blank record in the classifications table so that's why the error. But
is there a way to leave the classification blank or blank it out? Do I
have to put a record in the classifications lookup table, like (no
classification)? Thanks for any help or advice.

This is perfectly possible - just as you describe - without any need to
define a 'no classification' record in the classifications table. There is
something else you are doing wrong.
My guess is that you have something like
tblEmployee.ClassificationID which is a long integer with a default value of
zero. When you create the record, it tries to put zero in this field, and
of course you don't have a matching record where tblCalssification.ID=0.
So, certainly check any default values in your table design.

[Anthony England]

<pi********@hotmail.com> wrote in message
news:11**********************@z14g2000cwz.googlegr oups.com...

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications. If you set the RI constraint to none, then everything
will be fine.

Are you sure of what you are saying?

[David W. Fenton]

pi********@hotmail.com wrote in
news:11**********************@z14g2000cwz.googlegr oups.com:

if an employee can have no classification (and it's valid), then
you can't enforce referential integrity between employees and
classifications. If you set the RI constraint to none, then
everything will be fine.

Eh? All you need is for the foreign key to not be required. That
way, the FK can be Null, while still enforcing that any non-Null
value will be drawn from the PK of a related table.

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[pietlinden]

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications. If you set the RI constraint to none, then everything
will be fine.

If you choose to enforce RI, then you have to classify each employee.
That's the way RI works...

[manning_news]

There's no default value. For the user's benefit I store the
description, not an ID #.
Anthony England wrote:

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Using 2003. I've got a simple 1:M relationship for employees and
classifications. A classification can be assigned to many employees/an
employee can only have one classification. I created a form for adding
employees/classifications. The classification can be left blank if not
known. I created a 2nd form for modifying employees/classification.
If the classification is left blank, or it's blank out, the 3201
referential integrity error occurs. I understand that there is not a
blank record in the classifications table so that's why the error. But
is there a way to leave the classification blank or blank it out? Do I
have to put a record in the classifications lookup table, like (no
classification)? Thanks for any help or advice.

This is perfectly possible - just as you describe - without any need to
define a 'no classification' record in the classifications table. There is
something else you are doing wrong.
My guess is that you have something like
tblEmployee.ClassificationID which is a long integer with a default value of
zero. When you create the record, it tries to put zero in this field, and
of course you don't have a matching record where tblCalssification.ID=0.
So, certainly check any default values in your table design.

[manning_news]

There has to be a way to do this. I haven't tried this but I could
trap the error. But once it's trapped can I tell the mdb to ignore it
and leave the field blank?

[Anthony England]

<ma**********@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...

There's no default value. For the user's benefit I store the
description, not an ID #.
Anthony England wrote:

<ma**********@hotmail.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

> Using 2003. I've got a simple 1:M relationship for employees and
> classifications. A classification can be assigned to many employees/an
> employee can only have one classification. I created a form for adding
> employees/classifications. The classification can be left blank if not
> known. I created a 2nd form for modifying employees/classification.
> If the classification is left blank, or it's blank out, the 3201
> referential integrity error occurs. I understand that there is not a
> blank record in the classifications table so that's why the error. But
> is there a way to leave the classification blank or blank it out? Do I
> have to put a record in the classifications lookup table, like (no
> classification)? Thanks for any help or advice.

This is perfectly possible - just as you describe - without any need to
define a 'no classification' record in the classifications table. There
is
something else you are doing wrong.
My guess is that you have something like
tblEmployee.ClassificationID which is a long integer with a default value
of
zero. When you create the record, it tries to put zero in this field,
and
of course you don't have a matching record where tblCalssification.ID=0.
So, certainly check any default values in your table design.

If that is not the problem, then you could try and narrow down the cause of
the error. Without using any forms, but just entering data in the tables
directly, can you create an employee with no classification? If not then we
need to look at the table structure in greater detail. If you can, then it
is the form design at fault.

[Anthony England]

<pi********@hotmail.com> wrote in message
news:11*********************@g14g2000cwa.googlegro ups.com...

if an employee can have no classification (and it's valid), then you
can't enforce referential integrity between employees and
classifications. If you set the RI constraint to none, then everything
will be fine.

If you choose to enforce RI, then you have to classify each employee.
That's the way RI works...

If you create the following tables:

tblContact:
ConID Autonumber, PK
ConName Text, Length 50, Required, Do not allow zero length, Indexed No
Duplicates
ConType Long Integer, Not Required, Indexed Duplicates OK

tblType:
TypID Autonumber, PK
TypName Text, Length 50, Required, Do not allow zero length, Indexed No
Duplicates
.... you can enforce RI with a one to many relationship between tblType and
tblContact. This means that each contact can have a type - it doesn't have
to, but if it does have one, then it must be one in the table tblType.
That's the way RI works on my machine - but let me know if you cannot
reproduce this structure on yours.

[David W. Fenton]

ma**********@hotmail.com wrote in
news:11**********************@f14g2000cwb.googlegr oups.com:

Using 2003. I've got a simple 1:M relationship for employees and
classifications. A classification can be assigned to many
employees/an employee can only have one classification. I created
a form for adding employees/classifications. The classification
can be left blank if not known. I created a 2nd form for
modifying employees/classification. If the classification is left
blank, or it's blank out, the 3201 referential integrity error
occurs. I understand that there is not a blank record in the
classifications table so that's why the error. But is there a way
to leave the classification blank or blank it out? Do I have to
put a record in the classifications lookup table, like (no
classification)?

Did you define the classification field as required?

--
David W. Fenton http://www.dfenton.com/
usenet at dfenton dot com http://www.dfenton.com/DFA/

[manning_news]

No, it's not required. Here's the table setup:

tblEmployees:
ID, autonumber, PK
LastName, text, 50
FirstName, text, 50
Classification, text, 50, not required, not indexed, zero-length
allowed

tlkpClassifications:
Classification, text, 50, PK

[manning_news]

Forgot to add that I can enter an employee record directly into the
table, then blank out the classification. So it must be something in
the form design like Anthony England suggested. FWIW, it's an unbound
form. I use DAO to write to the employee table.

[Anthony England]

<ma**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Forgot to add that I can enter an employee record directly into the
table, then blank out the classification. So it must be something in
the form design like Anthony England suggested. FWIW, it's an unbound
form. I use DAO to write to the employee table.

Well that makes a huge difference. If it is unbound then presumably the
error occurs when you try to save the record and not directly after you edit
the control (you don't say whether this is a textbox or combo or what).
There are a number of ways you could be trying to save the record and you
haven't posted any code.
Two things come to mind:
1. My current guess is that instead of null as the classification, which
would be allowed, you are trying to save a zero-length string.
2. Before anyone else asks: " are you really sure unbound forms are
offering you advantages over bound ones?"

[manning_news]

1. Thanks. That was the problem

2. Poor choice on my part. There was no reason for it to be unbound.
I changed it to bound.
Anthony England wrote:

<ma**********@hotmail.com> wrote in message
news:11**********************@g14g2000cwa.googlegr oups.com...

Forgot to add that I can enter an employee record directly into the
table, then blank out the classification. So it must be something in
the form design like Anthony England suggested. FWIW, it's an unbound
form. I use DAO to write to the employee table.

Well that makes a huge difference. If it is unbound then presumably the
error occurs when you try to save the record and not directly after you edit
the control (you don't say whether this is a textbox or combo or what).
There are a number of ways you could be trying to save the record and you
haven't posted any code.
Two things come to mind:
1. My current guess is that instead of null as the classification, which
would be allowed, you are trying to save a zero-length string.
2. Before anyone else asks: " are you really sure unbound forms are
offering you advantages over bound ones?"