Link to home
Start Free TrialLog in
Avatar of Eduardo Fuerte
Eduardo FuerteFlag for Brazil

asked on

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
 User generated image
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.
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

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
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
Avatar of Eduardo Fuerte

ASKER

Hi


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

User generated image
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#).

User generated image
Exception translated:

It's not possible to add ou modify register, since it's necessary it have a register related in tblAutores
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
Can you also open tblBooks in design and check that the three Index values do not have required set to Yes.


Kelvin
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
... and the columns have the required clause set to no.
and just in case, RI is fired when editing the line directly inside Acceess

(better MDB file)
MasterFile.mdb
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
@Kelvin

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

lib2006
ASKER CERTIFIED SOLUTION
Avatar of Kelvin Sparks
Kelvin Sparks
Flag of New Zealand image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!

Thank you very much.