I
iluvhk86
How can I change the referential integrity to zero-to-one, zero-to-many?
Please respond asap!!!!
Please respond asap!!!!
You are using an out of date browser. It may not display this or other websites correctly.
You should upgrade or use an alternative browser.
You should upgrade or use an alternative browser.
J
John W. Vinson
How can I change the referential integrity to zero-to-one, zero-to-many?
Please respond asap!!!!
There is no such thing as either zero to one or zero to many. A nonexistant
record (the "zero") can't be related to ANYTHING.
Relationships will be one to one - strictly speaking, one to (zero or one)
since a parent record can exist without a related child record - if the
joining field has a unique Index (such as a primary key) in both tables. A
relationship will be one to many (again, one to (zero, one or many) - if the
parent table's linking field has a unique index and the corresponding field in
the child table has a nonunique index. Such an index will be created by Access
when the relationship is established.
John W. Vinson [MVP]
6
'69 Camaro
Hi.
You can't change the referential integrity to zero-to-one or zero-to-many.
(Referential integrity between two tables is either enforced or it is not.
There's no numerical assignment to referential integrity.) You can,
however, change the cardinality of the relationship between two tables.
A 0:1 cardinality requires a 1:1 relationship, with referential integrity
not enforced between the two tables, with both entities (tables) being
optional (i.e., one side can exist without the other related entity).
Either triggers or application logic is required to limit the number of
related rows.
A 0:M cardinality requires a 1:M relationship, with referential integrity
not enforced between the two tables if both entities (tables) are optional
(i.e., one side can exist without the other related entity), or with
referential integrity enforced between the two tables if the many side is
mandatory. This requires a nullable foreign key column on the many side
that references the one side.
On a Saturday night?!! Did you bring pepperoni pizza and plenty of soda
pop? If not, fuggetaboutit.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
How can I change the referential integrity to zero-to-one, zero-to-many?
You can't change the referential integrity to zero-to-one or zero-to-many.
(Referential integrity between two tables is either enforced or it is not.
There's no numerical assignment to referential integrity.) You can,
however, change the cardinality of the relationship between two tables.
A 0:1 cardinality requires a 1:1 relationship, with referential integrity
not enforced between the two tables, with both entities (tables) being
optional (i.e., one side can exist without the other related entity).
Either triggers or application logic is required to limit the number of
related rows.
A 0:M cardinality requires a 1:M relationship, with referential integrity
not enforced between the two tables if both entities (tables) are optional
(i.e., one side can exist without the other related entity), or with
referential integrity enforced between the two tables if the many side is
mandatory. This requires a nullable foreign key column on the many side
that references the one side.
Please respond asap!!!!
On a Saturday night?!! Did you bring pepperoni pizza and plenty of soda
pop? If not, fuggetaboutit.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
I
iluvhk86
So in the relationships window, how would these relationships look like (as
one-to-one relationship or one-to-many relationship)?
Thank you so much.
one-to-one relationship or one-to-many relationship)?
Thank you so much.
6
'69 Camaro
Hi.
The 0:1 relationship would show as "One-to-One" in the "Relationship Type"
section at the bottom of the dialog window. The "Enforce Referential
Integrity" check box would be unchecked.
The 0:M relationship would show as "One-to-Many" in the "Relationship Type"
section at the bottom of the dialog window. The "Enforce Referential
Integrity" check box would be unchecked if the many side is optional and
checked if the many side is mandatory.
Since Jet doesn't support triggers, you'll have to rely on application logic
to keep things straight when referential integrity isn't enforced and when
limiting the number of related rows, meaning that the integrity of the data
is at risk because the relational database engine isn't handling these
operations.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
So in the relationships window, how would these relationships look like
(as
one-to-one relationship or one-to-many relationship)?
The 0:1 relationship would show as "One-to-One" in the "Relationship Type"
section at the bottom of the dialog window. The "Enforce Referential
Integrity" check box would be unchecked.
The 0:M relationship would show as "One-to-Many" in the "Relationship Type"
section at the bottom of the dialog window. The "Enforce Referential
Integrity" check box would be unchecked if the many side is optional and
checked if the many side is mandatory.
Since Jet doesn't support triggers, you'll have to rely on application logic
to keep things straight when referential integrity isn't enforced and when
limiting the number of related rows, meaning that the integrity of the data
is at risk because the relational database engine isn't handling these
operations.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
I
iluvhk86
Thank you so much for your help, I will take your advice. This is for a
project I am doing for my class, and this problem has slowed our group down.
I really really appreciate your help.
project I am doing for my class, and this problem has slowed our group down.
I really really appreciate your help.
6
'69 Camaro
Hi.
You're welcome.
Even though you forgot to bring the pizza, and I don't normally help people
do their homework in the newsgroups, I made an exception because I had the
same problem when I was in school. Ultimately, you're going to discover
that Access can't adequately represent these two data models, because the
optional entity requires a NULL column, but the "One-to-One" and
"One-to-Many" relationship types won't show unless the column is Required
(no NULL's allowed), so it's a catch-22. If the column is designed with not
Required, the relationship type will show up as "Indeterminate."
When we informed our instructor of this, he told us, "You're not supposed to
do that part of your homework in Access. You'll have to log into the
university's Unix computer and enter the commands in the university's
database." So we had to use a command line interface to enter the SQL
commands in a much more complex RDBMS, not an easy Access GUI for these
relationships.
Good luck with it.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
Thank you so much for your help
You're welcome.
This is for a
project I am doing for my class, and this problem has slowed our group
down.
Even though you forgot to bring the pizza, and I don't normally help people
do their homework in the newsgroups, I made an exception because I had the
same problem when I was in school. Ultimately, you're going to discover
that Access can't adequately represent these two data models, because the
optional entity requires a NULL column, but the "One-to-One" and
"One-to-Many" relationship types won't show unless the column is Required
(no NULL's allowed), so it's a catch-22. If the column is designed with not
Required, the relationship type will show up as "Indeterminate."
When we informed our instructor of this, he told us, "You're not supposed to
do that part of your homework in Access. You'll have to log into the
university's Unix computer and enter the commands in the university's
database." So we had to use a command line interface to enter the SQL
commands in a much more complex RDBMS, not an easy Access GUI for these
relationships.
Good luck with it.
HTH.
Gunny
See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips and tutorials.
Blogs: www.DataDevilDog.BlogSpot.com, www.DatabaseTips.BlogSpot.com
http://www.Access.QBuilt.com/html/expert_contributors2.html for contact
info.
D
David W. Fenton
How can I change the referential integrity to zero-to-one,
zero-to-many? Please respond asap!!!!
You can't have a direct relationship of that type if the foreign-key
field is required.
It can be modelled with an intermediate many-to-many join table if
the FK is required.
D
David W. Fenton
Ultimately, you're going to discover
that Access can't adequately represent these two data models,
because the optional entity requires a NULL column, but the
"One-to-One" and "One-to-Many" relationship types won't show
unless the column is Required (no NULL's allowed), so it's a
catch-22.
Huh? You can have a 1:N relationship without the FK field being
required, i.e., allowing it to be Null.
If the column is designed with not
Required, the relationship type will show up as "Indeterminate."
Not true. It's the *indexes* on either side that determine the type
of relationship, and only when there is no index or when both
indexes are non-unique will it show up as indeterminate.
When we informed our instructor of this, he told us, "You're not
supposed to do that part of your homework in Access. You'll have
to log into the university's Unix computer and enter the commands
in the university's database." So we had to use a command line
interface to enter the SQL commands in a much more complex RDBMS,
not an easy Access GUI for these relationships.
In the comments above, you're mistaken about Jet's RI. You *can*
model some of this by using a non-required FK field. That takes care
of 0:N. On the other hand, I'm not sure what happens if you use the
same logic to attempt to implement 0:1.
In any event, with required fields, you can model either type of
relationship with an N:N join table between them. However, it will
require some code, I think.
Ask a Question
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.