K Feening
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.Sql Exception'
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
Updating a record
**** SUMMARY ****
MESSAGE: 'The record cannot be deleted as 'Agreements' contains related records'
LEVEL:1 TYPE: 'System.Data.SqlClient.Sql
MESSAGE: 'The UPDATE statement conflicted with the REFERENCE constraint
"Agreements_BoatOwner_FK".
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
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.
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.
ASKER
sorry that's what I did and got the error
Agreements_BoatOwner_FK tried to change to Cascade
Agreements_BoatOwner_FK tried to change to Cascade
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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".
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".