/*
Applies recommended primary keys, foreign keys and indexes to Umbraco Forms tables relating to "forms in the database" (i.e.
when configuration key StoreUmbracoFormsInDb = true).
This replicates for SQL Server the migration AddFormKeysAndIndexes.
*/
-- Adds unique constraint to UFForms.
ALTER TABLE dbo.UFForms
ADD CONSTRAINT UK_UFForms_Key UNIQUE NONCLUSTERED
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds unique constraint to UFDataSource.
ALTER TABLE dbo.UFDataSource
ADD CONSTRAINT UK_UFDataSource_Key UNIQUE NONCLUSTERED
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds unique constraint to UFPrevalueSource.
ALTER TABLE dbo.UFPrevalueSource
ADD CONSTRAINT UK_UFPrevalueSource_Key UNIQUE NONCLUSTERED
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds unique constraint to UFWorkflows.
ALTER TABLE dbo.UFWorkflows
ADD CONSTRAINT UK_UFWorkflows_Key UNIQUE NONCLUSTERED
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds index on join field in UFWorkflows.
CREATE NONCLUSTERED INDEX IX_UFWorkflows_FormId ON dbo.UFWorkflows
(
FormId ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO/*
Reverts application of recommended primary keys, foreign keys and indexes to Umbraco Forms tables relating to "forms in the database" (i.e.
when configuration key StoreUmbracoFormsInDb = true).
This reverts for SQL Server the migration AddFormKeysAndIndexes and can be used for rolling that back in testing.
*/
-- Reverts addition of unique constraint to UFForms.
ALTER TABLE dbo.UFForms
DROP CONSTRAINT IF EXISTS UK_UFForms_Key
GO
-- Reverts addition of unique constraint to UFPrevalueSource.
ALTER TABLE dbo.UFDataSource
DROP CONSTRAINT IF EXISTS UK_UFDataSource_Key
GO
-- Reverts addition of unique constraint to UFPrevalueSource.
ALTER TABLE dbo.UFPrevalueSource
DROP CONSTRAINT IF EXISTS UK_UFPrevalueSource_Key
GO
-- Reverts addition of unique constraint to UFWorkflows.
ALTER TABLE dbo.UFWorkflows
DROP CONSTRAINT IF EXISTS UK_UFWorkflows_Key
GO
-- Reverts addition of index on foreign key fields in UFWorkflows.
DROP INDEX IF EXISTS IX_UFWorkflows_FormId ON dbo.UFWorkflows
GO
-- Reverts addition of index on foreign key fields in UFWorkflows.
DROP INDEX IF EXISTS IX_UFWorkflows_FormId ON dbo.UFWorkflows
GO/*
Reverts application of recommended primary keys, foreign keys and indexes to core Umbraco Forms tables.
This reverts for SQL Server the migration AddRecordKeysAndIndexes and can be used for rolling that back in testing.
*/
-- Reverts addition of relationship between UFRecords and UFRecordFields.
ALTER TABLE dbo.UFRecordFields
DROP CONSTRAINT IF EXISTS FK_UFRecordFields_UFRecords_Record
GO
-- Reverts addition of primary keys to UFRecordData* tables.
ALTER TABLE dbo.UFRecordDataBit
DROP CONSTRAINT IF EXISTS PK_UFRecordDataBit
GO
ALTER TABLE dbo.UFRecordDataDateTime
DROP CONSTRAINT IF EXISTS PK_UFRecordDataDateTime
GO
ALTER TABLE dbo.UFRecordDataInteger
DROP CONSTRAINT IF EXISTS PK_UFRecordDataInteger
GO
ALTER TABLE dbo.UFRecordDataLongString
DROP CONSTRAINT IF EXISTS PK_UFRecordDataLongString
GO
-- Reverts addition of relationship between UFRecordFields and UFREcordData* tables.
ALTER TABLE dbo.UFRecordDataBit
DROP CONSTRAINT IF EXISTS FK_UFRecordDataBit_UFRecordFields_Key
GO
ALTER TABLE dbo.UFRecordDataDateTime
DROP CONSTRAINT IF EXISTS FK_UFRecordDataDateTime_UFRecordFields_Key
GO
ALTER TABLE dbo.UFRecordDataInteger
DROP CONSTRAINT IF EXISTS FK_UFRecordDataInteger_UFRecordFields_Key
GO
ALTER TABLE dbo.UFRecordDataLongString
DROP CONSTRAINT IF EXISTS FK_UFRecordDataLongString_UFRecordFields_Key
GO
-- Reverts addition of index on foreign key fields in UFREcordData* tables.
DROP INDEX IF EXISTS IX_UFRecordDataBit_Key ON dbo.UFRecordDataBit
GO
DROP INDEX IF EXISTS IX_UFRecordDataDateTime_Key ON dbo.UFRecordDataDateTime
GO
DROP INDEX IF EXISTS IX_UFRecordDataInteger_Key ON dbo.UFRecordDataInteger
GO
DROP INDEX IF EXISTS IX_UFRecordDataLongString_Key ON dbo.UFRecordDataLongString
GO
-- Reverts addition of primary key to UFUserSecurity
ALTER TABLE dbo.UFUserSecurity
DROP CONSTRAINT IF EXISTS PK_UFUserSecurity
GO
-- Reverts addition of primary key to UFUserFormSecurity
ALTER TABLE dbo.UFUserFormSecurity
DROP CONSTRAINT IF EXISTS PK_UFUserFormSecurity
GO
-- Reverts addition of unique constraint to UFUserFormSecurity across user/form fields.
ALTER TABLE dbo.UFUserFormSecurity
DROP CONSTRAINT IF EXISTS UK_UFUserFormSecurity_User_Form
GO/*
Applies recommended primary keys, foreign keys and indexes to core Umbraco Forms tables.
This replicates for SQL Server the migration AddRecordKeysAndIndexes.
*/
-- Adds relationship between UFRecords and UFRecordFields.
ALTER TABLE dbo.UFRecordFields
ADD CONSTRAINT
FK_UFRecordFields_UFRecords_Record FOREIGN KEY
(
Record
) REFERENCES dbo.UFRecords
(
Id
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
-- Adds primary keys to UFRecordData* tables.
ALTER TABLE dbo.UFRecordDataBit
ADD CONSTRAINT
PK_UFRecordDataBit PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.UFRecordDataDateTime
ADD CONSTRAINT
PK_UFRecordDataDateTime PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.UFRecordDataInteger
ADD CONSTRAINT
PK_UFRecordDataInteger PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
ALTER TABLE dbo.UFRecordDataLongString
ADD CONSTRAINT
PK_UFRecordDataLongString PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds relationship between UFRecordFields and UFREcordData* tables.
ALTER TABLE dbo.UFRecordDataBit
ADD CONSTRAINT
FK_UFRecordDataBit_UFRecordFields_Key FOREIGN KEY
(
[Key]
) REFERENCES dbo.UFRecordFields
(
[Key]
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.UFRecordDataDateTime
ADD CONSTRAINT
FK_UFRecordDataDateTime_UFRecordFields_Key FOREIGN KEY
(
[Key]
) REFERENCES dbo.UFRecordFields
(
[Key]
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.UFRecordDataInteger
ADD CONSTRAINT
FK_UFRecordDataInteger_UFRecordFields_Key FOREIGN KEY
(
[Key]
) REFERENCES dbo.UFRecordFields
(
[Key]
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
ALTER TABLE dbo.UFRecordDataLongString
ADD CONSTRAINT
FK_UFRecordDataLongString_UFRecordFields_Key FOREIGN KEY
(
[Key]
) REFERENCES dbo.UFRecordFields
(
[Key]
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
-- Adds index on foreign key fields in UFREcordData* tables.
CREATE NONCLUSTERED INDEX IX_UFRecordDataBit_Key ON dbo.UFRecordDataBit
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_UFRecordDataDateTime_Key ON dbo.UFRecordDataDateTime
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_UFRecordDataInteger_Key ON dbo.UFRecordDataInteger
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX IX_UFRecordDataLongString_Key ON dbo.UFRecordDataLongString
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds primary key to UFUserSecurity.
ALTER TABLE dbo.UFUserSecurity
ADD CONSTRAINT
PK_UFUserSecurity PRIMARY KEY CLUSTERED
(
[User]
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds primary key to UFUserFormSecurity.
ALTER TABLE dbo.UFUserFormSecurity
ADD CONSTRAINT
PK_UFUserFormSecurity PRIMARY KEY CLUSTERED
(
Id
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
-- Adds unique constraint to UFUserFormSecurity across user/form fields.
ALTER TABLE dbo.UFUserFormSecurity
ADD CONSTRAINT UK_UFUserFormSecurity_User_Form UNIQUE NONCLUSTERED
(
[User] ASC,
[Form] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOIn this article, you will find information about Umbraco Forms-related health checks that can be run from the Umbraco backoffice to ensure that your installation is running seamlessly.
Read the Health Check article to learn more about the feature in general.
Running this health check will verify whether the database tables for the Umbraco Forms installation are all set up correctly with the proper data integrity checks.
In this section, you can learn more about the background for adding this check, as well as how to use and understand the results.
A health check was introduced to confirm the Umbraco Forms database tables are all set up with the expected data integrity checks - i.e. primary keys, foreign keys and unique constraints.
In most cases, you can expect them all to be in place without any developer intervention. For new installs, the database schema is initialized with all the necessary integrity constraints. And for upgrades, any new schema changes are automatically applied.
There remains the possibility though that not all will be in place for a particular installation. For example, this could happen if a constraint is added in a new version. It can't be added via an automated migration due to existing data integrity issues.
In particular, prior to version 8.7, there were a number of tables that weren't defined as strictly as they should be in this area. So we've added some primary key, foreign key and unique constraints with this version. If you've been running a version prior to this and are upgrading, these schema updates will be applied automatically unless there is existing data in the tables that prevent them from being added.
There shouldn't be - but without these constraints in place it's always possible for an application bug to exist that allows for example the creation of duplicate records, or the orphaning of records, that aren't correct. This is the reason for the constraints to exist, and why we want to ensure they are in place.
To run the health check:
Navigate to the Health Check dashboard in the Settings section in the Umbraco backoffice.
Click on the Forms button and select Check Group. You'll see a result that looks something like this: \
If you have a full set of green ticks, then you're all good - and no need to read on!
If you have one or more red crosses though, that means a particular constraint wasn't able to be applied via the automatic schema migrations when you installed a new version of Umbraco Forms, due to existing data issues.
It isn't essential that they are resolved - the package can and does function correctly without them - but for reasons of ensuring data integrity and performance, it is recommended that they are.
When Umbraco Forms installs an upgrade, it will attempt to apply any schema changes. If though, the update isn't essential, and it can't proceed due to existing data integrity issues, the failed update will be logged and then the rest of the migration will continue.
As well as in the log files, such issues will be visible via the health check and will need to be resolved by applying scripts directly to the database.
To support this, we provide the following SQL scripts:
Apply database integrity schema changes for 8.7.0+ -
Apply database integrity schema changes for 8.7.0+ (Forms in database tables) -
The first of these provides the SQL statements required to apply the schema updates for 8.7.0+ to the common Umbraco Forms tables. The second applies to those tables used for when Forms are stored in the database, and hence only need to be applied if that option is configured.
To take an example, let's say that via the health check results you can see that the "Unique constraint on table 'UFForms', column 'Key' is missing."
If you look in the SQL script you'll see that in order to apply this directly to the database, you would need to run the following SQL statement:
If you run it though, you'll see the reason why the migration that ran when Umbraco Forms was upgraded couldn't apply the change:
The constraint can't be applied if there are existing duplicate values, so first they need to be found and removed.
To find duplicate values in the 'Key' field in this table you can run the following SQL statement:
Running the statement above will list out the 'Key' fields that are duplicated in the table.
To see the full details of the duplicate records, you can use this query:
From the Id field you can identify the Form records that are duplicated and should be removed, and delete the records. To check you have found them all, run one of the above queries again, and confirm you find no records returned.
Finally you can run the ALTER TABLE... statement shown above to apply the constraint, and confirm via the health check that it's now in place.
By repeating similar steps as required, you'll be able to ensure that all recommended keys, constraints and indexes are in place.
If for any reason you wish to revert the changes - perhaps when testing these updates in a non-production environment - reversion scripts for all the 8.7 updates are also provided:
To support this, we provide the following SQL scripts:
Revert database integrity schema changes for 8.7.0+ -
Revert database integrity schema changes for 8.7.0+ (Forms in database tables) -
-- Adds unique constraint to UFForms.
ALTER TABLE dbo.UFForms
ADD CONSTRAINT UK_UFForms_Key UNIQUE NONCLUSTERED
(
[Key] ASC
) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GOThe CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo.UFForms' and the index name 'UK_UFForms_Key'. The duplicate key value is (...).SELECT [Key]
FROM UFForms
GROUP BY [Key]
HAVING COUNT(*) > 1SELECT *
FROM UFForms
WHERE [Key] IN (SELECT [Key]
FROM UFForms
GROUP BY [Key]
HAVING COUNT(*) > 1
)