relationship - referencial integrety

[Rick Brandt]

I'm a bit confused about one of the relationships in my database,
which is for keeping records of the music library at a music school...

I have one field, method, that is for entering the name of a teaching
method or a series of books.. I have created a related table which
will contain all the method or series names, which will then be
picked from a combo box during data entry..

my problem is that many books are not part of a method or series, so
sometimes this field will be empty.. the field is defined as a
number, because it contains the methodID from the related table, so
it appears as a 0 in the main table, an entry which does not exist in
the related table..

thus, setting up the relationships, it won't let me set referential
integrity (RI) and I'm not sure what to do about it.. I could make an
entry in the related table "n/a" so that there would always be
something in the field.. then it would let me set RI..

or maybe its not important as long as I set the cascading updates,
etc.. I'm not sure exactly what effect it will have if I don't set
RI... can someone give me an idea what possible consequences it will have??

much thanks..
Helen in Canada

If you want to limit allowable entries to those in the lookup table then you
must have RI enabled. If picking from the list is merely a convenience then you
don't need to enable RI.

You can also enable RI and use Null for that field instead of zero. RI will
care about the zero. It will not care about a Null.

 

[Helen Martin]

I'm a bit confused about one of the relationships in my database, which
is for keeping records of the music library at a music school...

I have one field, method, that is for entering the name of a teaching
method or a series of books.. I have created a related table which will
contain all the method or series names, which will then be picked from a
combo box during data entry..

my problem is that many books are not part of a method or series, so
sometimes this field will be empty.. the field is defined as a number,
because it contains the methodID from the related table, so it appears
as a 0 in the main table, an entry which does not exist in the related
table..

thus, setting up the relationships, it won't let me set referential
integrity (RI) and I'm not sure what to do about it.. I could make an
entry in the related table "n/a" so that there would always be something
in the field.. then it would let me set RI..

or maybe its not important as long as I set the cascading updates, etc..
I'm not sure exactly what effect it will have if I don't set RI...
can someone give me an idea what possible consequences it will have??

much thanks..
Helen in Canada

 

[Michael Gramelspacher]

I'm a bit confused about one of the relationships in my database, which
is for keeping records of the music library at a music school...

I have one field, method, that is for entering the name of a teaching
method or a series of books.. I have created a related table which will
contain all the method or series names, which will then be picked from a
combo box during data entry..

my problem is that many books are not part of a method or series, so
sometimes this field will be empty.. the field is defined as a number,
because it contains the methodID from the related table, so it appears
as a 0 in the main table, an entry which does not exist in the related
table..

thus, setting up the relationships, it won't let me set referential
integrity (RI) and I'm not sure what to do about it.. I could make an
entry in the related table "n/a" so that there would always be something
in the field.. then it would let me set RI..

or maybe its not important as long as I set the cascading updates, etc..
I'm not sure exactly what effect it will have if I don't set RI...
can someone give me an idea what possible consequences it will have??

much thanks..
Helen in Canada

Do not set a default value and try running an update query
to change 0's to null and see if that helps.
UPDATE Table1 SET Table1.MethodID = Null
WHERE (((Table1.MethodID)=0));

 

[John W. Vinson]

the field is defined as a number,
because it contains the methodID from the related table, so it appears
as a 0 in the main table

Well... only if you leave in Access' stupid "default default", which assumes
that all Number fields must have a default value of zero.

You can remove the Default Value property of this field (new entries will
default to NULL, which will not interfere with your referential integrity),
and you can run an Update query updating the existing zero values to NULL.

John W. Vinson [MVP]