If you’re getting SQL Server error 1785 that reads something like “Introducing FOREIGN KEY constraint ‘FK__Name’ on table ‘Department’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints“, it’s probably because you’ve specified the CASCADE option when trying to create a foreign key with a circular reference, or one that has multiple cascade paths (perhaps your child table has multiple foreign keys to the same primary key on the parent table).
Tag: errors
Fix Error 1989 “Cannot enable foreign key constraint … as index … on referenced key is disabled” in SQL Server
If you’re getting SQL Server error 1989 that reads something like “Cannot enable foreign key constraint ‘FK_Jobs_Employees’ as index ‘PK__Employee__7AD04FF1A39ECAB1’ on referenced key is disabled“, it’s probably because you’re trying to enable a foreign key, when the index that it references is disabled.
For example, if you’ve disabled a primary key on the parent table, and then try to enable a foreign key that references that primary key, then you’ll get this error.
To fix this issue, enable the index for the primary key/referenced key before enabling the foreign key.
Continue readingFix Error 2714 “There is already an object named ‘…’ in the database” in SQL Server
If you’re getting SQL Server error 2714 that reads something like “There is already an object named ‘Actors’ in the database” it seems that you’re trying to create an object that already exists.
It could be that you didn’t know that the object had already been created. Or it could be that you’re trying to create a different object, but you’re inadvertently using the same name that another object has. Or it could be that you want both objects to have the same name, but a different schema. In this case, perhaps you inadvertently omitted the schema name from the object.
Continue reading“Incorrect syntax” Error When Creating a Filtered Index in SQL Server due to an Unsupported Operator
If you’re getting the “Incorrect syntax…” error in SQL Server when trying to create a filtered index, it could be that you’re using an unsupported operator.
Filtered indexes in SQL Server only support simple comparison operators. If you use an unsupported operator or expression, you’ll likely get the dreaded “Incorrect syntax” error.
To fix, either use a supported operator, or create a view.
Continue readingFix Error “cannot truncate a table referenced in a foreign key constraint” in PostgreSQL
When attempting to truncate a table in PostgreSQL, you might encounter the error “cannot truncate a table referenced in a foreign key constraint“. This is the default behaviour for the TRUNCATE statement whenever you try to truncate a table that is referenced by another table through a foreign key relationship.
If you want to truncate both tables, you can use the CASCADE option of the TRUNCATE statement. Alternatively, you could truncate both tables at the same time.
Fix Error 1988 “Cannot rebuild clustered index … online because it is disabled” in SQL Server
If you’re getting SQL Server error 1988 that reads something like “Cannot rebuild clustered index ‘PK__Employee__7AD04FF1A39ECAB1’ online because it is disabled.“, it’s probably because you’re trying to rebuild a disabled clustered index online.
We can’t rebuild a disabled clustered index with (ONLINE = ON).
Fix Error Msg 129 “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100” in SQL Server
If you’re getting SQL Server error Msg 129 that reads “Fillfactor 0 is not a valid percentage; fillfactor must be between 1 and 100“, it appears that you’re trying to set the fillfactor of an index to zero.
While it’s true that zero is the default setting, we can’t actually explicitly set the fillfactor to zero when creating or rebuilding an index.
But all is not lost. Below are two solutions to this problem.
Continue readingFix Error “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server
If you’re getting an error that reads “The configuration option ‘fill factor’ does not exist, or it may be an advanced option” in SQL Server, it appears that you’re trying to view or set the default fill factor configuration option.
In SQL Server, fill factor is considered an advanced configuration option. By default, advanced options aren’t available for viewing and changing. However, we can use the following technique to make them available.
Fix Error 3723 “An explicit DROP INDEX is not allowed on index” in SQL Server
If you’re getting SQL Server error 3723 that reads “An explicit DROP INDEX is not allowed on index…” it’s probably because you’re trying to drop a PRIMARY KEY or UNIQUE constraint by dropping its index.
Possible Reason for Error 1914 “object is not a user table or view” in SQL Server
If you’re getting an error that reads something like “Index cannot be created on object ‘Customers’ because the object is not a user table or view” it could be that you’re trying to create an index, but there’s also a synonym of the same name (but in a different schema). It would appear that SQL Server thinks that you’re trying to create the index on the synonym instead of the table or view of the same name.
Continue reading