Overriding Referential Inegrity

[BeeGeesus]

I have designed a form providing information for several tables where the primary keys are used as a lookup. I keep running into the violation issue. I am looking for a way to copy a value to the primary table and then continue with data entry using the form or to bypass the referential integrity. Any Ideas.

 

[georgedwilkinson]

You haven't really told us what you're trying to do. Referential integrity is there for a reason. If you don't need RI, turn it off.

For a better answer, give more information. What do your tables look like? What are you trying to do and why?

 

[SOS]

Yeah, you either HAVE Referential Integrity or you don't. There is no middle ground there.

 

[BeeGeesus]

Sorry I didnt explain myself properly. The database is too keep a record of people, vehicles and numbers. When we take on a new customer the unique details like name and vehicle are used as the PK. The start form contains the FK. I want to add new PK's to the relevant tables without the violations. Does this make more sense.

 

[georgedwilkinson]

Not at all.

As a matter of making your life easier, you should probably go with artificial (AKA surrogate) primary keys (with autonumber type). Make your current primary keys into unique indexes, then use the number type as your FK in the related table.

Second, there is absolutely no reason in a properly normalized database to bypass RI. So if you must do it to make your system work, you have a problem with your design. Since you have posted no aspect of your design here for us to inspect, we cannot advise you further.

<deja-vu>
For a better answer, give more information. What do your tables look like? What are you trying to do and why?
</deja-vu>

 

[gemma-the-husky]

with RI. it means one of them comes first - the 1 side of the RI link.

so if a driver is set as the one in a driver-vehicles link, then the driver comes first (ie one person can own multiple vehicles, but a vehicle MUST be owned by someone) - and you cant create the vehicle without a driver pre-existing

so this may be a design/normalisation issue

 

[BeeGeesus]

I have attached a zip file of the relevant part of my database. I thought I was making it easier by using Driver Name as the common key, but maybe this is wrong !!!!! Also this is in Access 2007 format. Have I over complicated the data base by splitting the tables into Driver Name (Lookup Table), Driver Number (Lookup Table), Address, Phone Numbers, Vehicle (Lookup Table) and Starting Details. The Same driver can use a different number and different car. I also need to keep a record of any address and phone number changes.