Link to home
Start Free TrialLog in
Avatar of K Feening
K FeeningFlag for Australia

asked on

SQL Server 2008 Error

Hi Experts I get following Error
Updating a record

**** SUMMARY ****
MESSAGE: 'The record cannot be deleted as 'Agreements' contains related records'
LEVEL:1     TYPE: 'System.Data.SqlClient.SqlException'
MESSAGE: 'The UPDATE statement conflicted with the REFERENCE constraint
"Agreements_BoatOwner_FK". The conflict occurred in database "MYDatabase",
table "dbo.Agreements", column 'BoatOwner'.
The statement has been terminated.'

Was told to

Go to 2008 Server Tables > then Design
Select Key then relationships select foreign key Agreements_BoatOwner_FK
then Insert and Update spacific and change update rule from no action to Cascade

Then this error

'Customers' table saved successfully
'Agreements' table
- Unable to create relationship 'Agreements_BoatOwner_FK'.  
Introducing FOREIGN KEY constraint 'Agreements_BoatOwner_FK' on table
'Agreements' may cause cycles or multiple cascade paths.
Specify ON DELETE NO ACTION or ON UPDATE NO ACTION,
or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.

What can I do to correct

Thanks
Avatar of Peter Chan
Peter Chan
Flag of Hong Kong image

Hi,
Please check table constraints. For this issue, there is FK constraint being violated, as the FK is actually referring to PK of one other table. Please check "Agreements_BoatOwner_FK".
This error generally occurs when the primary key of a table is updated but it is referenced by a foreign key from another table and the update specific is set to No action.

To fix this you need to change the foreign-key definition to Cascade.

> Right click your foreign key and select Modify.
> In the Foreign key relationships dialog under the INSERT and UPDATE specifics set the UPDATE rule on Cascade.
Avatar of K Feening

ASKER

sorry that's what I did and got the error

Agreements_BoatOwner_FK tried to change to Cascade
SOLUTION
Avatar of Nakul Vachhrajani
Nakul Vachhrajani
Flag of India 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
ASKER CERTIFIED SOLUTION
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
SOLUTION
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
Avatar of Nandu 45
Nandu 45

It's also possible that you're trying to use SQL Server Authentication without having enabled it. To fix this, right-click Properties on your server instance in SQL Server Management Studio, and update the security settings to include "SQL Server and Windows Authentication mode".