Unique index causing deletion of records from parent table

[Preka]

So, this is driving me up a wall. This is a SQL Server 2005 database using Access via ODBC as a front end for some very simple data entry forms for an extremely short term project of limited scope.

The relevant tables (or, really, an abstraction of them) are:
TblRelations:
RelID (PK, autonumber)
CompanyID(FK)
ParentID(FK)

TblCompany:
CompanyID (PK, autonumber)
(misc irrelevant fields, address, etc.)

TblParent:
ParentID(PK,autonumber)
(misc. irrelevant)

Every company should only be associated with a single parent, so a unique key index was created on the CompanyID field in tblRelations. Once that index is defined, however, deleting a record from tblRelations causes the parent record in tblCompany to also be deleted, but only when the deletion is performed from the Access. If I simply open the table in SQL Studio and delete the records from the table, it behaves exactly as I would expect - the record is deleted from tblRelations and the associated record on tblCompany is left intact.

What am I missing here?

 

[Preka]

Erm... that should read, "Only when the deletion is performed from Access".

My kingdom for an edit button.

 

[SQLBill]

I'm not familiar enough with Access, but there could be an option set that is causing Cascading Deletes. You might want to Google that or use Access Help to see if it says anything about that.

-SQLBill

The following is part of my signature block and is only intended to be informational.
Posting advice: FAQ481-4875

 

[kray4660]

From what I can recall, If referential integrity is enforced then this would happen (it's a feature).

 

[Preka]

Doesn't Referential integrity usually work in the other direction? I could understand deleting a field in the parent causing associated deletions in the child table to prevent orphaned entries, but why would deleting a record where a value is used as a foreign key cascade backwards and delete the primary key of a different table?

 

[Preka]

Ah... sorted it out. The form referencing the relations table is doing so via a query that links the relations table with the company table (for the purpose of displaying the miscellaneous company information on the form).

A SQL view would flat out not allow the deletion because it attempts to modify two tables. Access has no such constraint, but it knows that a one-many relationship shouldn't delete the "one" side when an associated "many" is deleted, so it allows the deletion with no ill effects.... until the unique index is added. The unique index redefines the relationship between the two tables as one-to-one, though, so it goes ahead and deletes both.