Furthermore to my search upon the issue I came across the following article:
https://support.managed.com/kb/a1968/...More specifically:
o correct this issue you can attempt the following:
Prior to doing this it is highly recommended that you backup your database.
Specifically with this error message recieved it is referencing the dbo.TabPermission and the FK_TabPermission_Roles foreign key constraint it may be others if there are other foreign keys having dependency issues
Log in to your database via SQL management studio
Select your database
On the table you want to make changes to you need to run the below SQL query this query will let you identify wether:
sp_help [table_name]
* The table name is without the dbo.
Find the table that the error message you are getting. Right-click the table name and select Design
Once you are in the design mode select the relationships button from (the one circled red):
On the Foreign Key Relationships window you will see the list of the foreign keys. The one for this example is for the FK_TabPermission_Roles foreign key. Select FK_TabPermission_Roles relationship
In the section named INSERT And Update Specific change the Delete Rule to Cascade
Doing this will not save any information to the database. You will need to select the Generate Change Script button (the one circled red):
Save the script to your desktop and leave the name the same
You will then need to run the script from within SQL Management Studio. While still in the database you are using. Select the New Query button and open the SQL file in notepad or any text editor and copy and paste the contents into the query window
#################################################################################################################################
llowed carefully the instructions given above but when I ran the script of step No.10, I'm getting the error:
Msg 3728, Level 16, State 1, Line 1
'FK_TabPermission_Roles' is not a constraint.
Msg 3727, Level 16, State 0, Line 1
Could not drop constraint. See previous errors.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
Msg 1785, Level 16, State 0, Line 1
Introducing FOREIGN KEY constraint 'FK_TabPermission_Roles' on table 'TabPermission' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
Msg 3902, Level 16, State 1, Line 1
The COMMIT TRANSACTION request has no corresponding BEGIN TRANSACTION.
The exported script reads:
/*
A Date
User: SomeUser
Server: x.x.x.x,port no
Database: InteliBrainCOM
Application:
*/
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TabPermission
DROP CONSTRAINT FK_TabPermission_Roles
GO
ALTER TABLE dbo.Roles SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.TabPermission ADD CONSTRAINT
FK_TabPermission_Roles FOREIGN KEY
(
RoleID
) REFERENCES dbo.Roles
(
RoleID
) ON UPDATE NO ACTION
ON DELETE CASCADE
GO
ALTER TABLE dbo.TabPermission SET (LOCK_ESCALATION = TABLE)
GO
COMMIT
What am I doing wrong?