• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 55
  • Last Modified:

Could you point how to deal with this intricated Relational Integrity when using MS-Access ?

Hi Experts

Could you point how to deal with this intricated Relational Integrity when using MS-Access ?

Accordingly to
 img001
Three columns of tblBooks must have relational integrity with tblAutores at same time, but just one (AutorEncarnadoIndex)  or two(AutorEspiritualIndex and AutorEncarnadoIndex) must be filled at same time.

If only one of  the 03 columns has "0" value the a insertion could not be performed.

If the relational integrity isn't flagged  to all the 03 columns an author that is still been used in a not relationed column, it can be deleted.

Any suggestion?

Thanks in advance.
0
Eduardo Fuerte
Asked:
Eduardo Fuerte
  • 8
  • 8
1 Solution
 
Kelvin SparksCommented:
From what I can understand, you need RI to the three columns in tblBook. Each Author index need to related to the Author index - correct?

In that case you must create three separate RI relationships. So you add the Author table three times and create a relationship to each of the three Author columns in your tblBooks.


Kelvin
0
 
Kelvin SparksCommented:
In addition to my post above, you should not use a default of 0 for no value. RI accepts the use of NULL for columns with no value. 0 Implies that it is a value - which in this case it technically incorrect.


Kelvin
0
 
Eduardo FuerteAuthor Commented:
Hi


So I put 03 copies of tblAutores, each one with a RI to one of the tblBooks columns.
Accordingly to:

img002
And changed the default values to null for the 03 columns in tblBooks.

If all the three columns in tblBooks are filled, Ok.

But if I fill just one column, the error arises (using C#).

img003
0
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
Eduardo FuerteAuthor Commented:
Exception translated:

It's not possible to add ou modify register, since it's necessary it have a register related in tblAutores
0
 
Kelvin SparksCommented:
Are you able to edit the row directly in Access? Are you running some sort of data layer between Access and C# (Entity Framework or similar)?


Kelvin
0
 
Kelvin SparksCommented:
Can you also open tblBooks in design and check that the three Index values do not have required set to Yes.


Kelvin
0
 
Eduardo FuerteAuthor Commented:
Hi


I'm using C# with OleDb - not using data layer.

I can edit the lines directly inside Access.

(The Access MDB file  is attached here, in case you want to have a look)
MasterFile.mdb
0
 
Eduardo FuerteAuthor Commented:
... and the columns have the required clause set to no.
0
 
Eduardo FuerteAuthor Commented:
and just in case, RI is fired when editing the line directly inside Acceess

(better MDB file)
MasterFile.mdb
0
 
Kelvin SparksCommented:
If you can edit the records directly in the database exactly as you try via your application, then I believe that the problem lies in your application and not the database. I am not a C# developer, so have a limited ability to assist you here.


Kelvin
0
 
Eduardo FuerteAuthor Commented:
@Kelvin

If I edit the line record directly in MS-Access the same error arises, so the problem isn't related to C# itself.
0
 
Kelvin SparksCommented:
Ah, OK I'll check out your db shortly and advise
0
 
Kelvin SparksCommented:
Database password?
0
 
Eduardo FuerteAuthor Commented:
Sorry

lib2006
0
 
Kelvin SparksCommented:
OK, have edited the database. I removed all DEfault values from the fields in question - just leave blank for NULL. I have also edited the relationships diagram to remove duplicate relationships to these.
MasterFile.mdb
0
 
Eduardo FuerteAuthor Commented:
Perfect!

Thank you very much.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 8
  • 8
Tackle projects and never again get stuck behind a technical roadblock.
Join Now