Common Laserfiche 7 SQL Database Constraints Encountered During the Laserfiche 6 to 7 Migration Process.

February 25, 2005 | KB: 1000893
Server (MSDE) 7, Server (MSSQL) 7, Server (Oracle) 7

Summary

Laserfiche 7 utilizes database contraints as a safeguard to prevent data corruption. This can occasionally lead to issues during the migration processes.

Previous versions of Laserfiche did not utilize as many constraints. This can lead to situations during the migration process where you will receive error messages stating that a row could not be created/migrated because of a constraint.

Laserfiche utilizes four basic types of constraints.

  • Check Constraints: This type of constraint limits the set of values that can be used. Check Constraints used by Laserfiche begin with the letters "CK."
  • Unique Constraints: This type of constraint prevents any two rows in a column or set of columns from having the same value. Unique Constraints used by Laserfiche begin with the letters "UNQ."
  • Primary Key Constraints: This type of constraint also prevents any two rows in a column or set of columns from having the same value. In addition, Primary keys cannot be null values. Primary key constraints used by Laserfiche begin with the letters "PK."
  • Foreign Key Constraints: This type of constraint controls the relationships between different tables. Values in a column or set of columns controlled by a foreign key must match existing values in a column or set of columns in another table specified by the constraint. Foreign key constraints used by Laserfiche begin with the letters "FK."

More Information

The following tables list the database contraints that you may encounter during migration.

Check Constraints

Constraint Name Description
CK_Toc_VolIndexed The IsIndexed value in the Toc table cannot be set to 1 when the VolumeId value is NULL.
CK_Toc_Shortcuts Shortcuts cannot point to templates or volumes.
CK_Elec_FileSize A FileSize value in the Elec table cannot be less than 0.
CK_PageNum A PageNum value in the Doc table cannot be less than 0.
CK_StoreId A StoreId value in the Doc table cannot be less than or equal to 0.
CK_ImageFileSize An ImageFileSize value in the Doc table cannot be less than 0.
CK_TextFileSize A TextFileSize value in the Doc table cannot be less than 0.
CK_Pos A Pos value in the TFields table cannot be less than 0.
CK_Type A Type value in the TFields table must be from the following set: CHAR, LIST, DATE, INTEGER, LONG INTEGER, or DATETIME.
CK_ItemIndex An ItemIndex value in the Lup table cannot be less than or equal to 0.
CK_Stamp_StampId A StampId value cannot be less than or equal to 0.
CK_Ann_ItemId A ItemId value in the Ann table cannot be less than or equal to 0.
CK_Ann_Type A Type value in the Ann table must a value between 1 and 7.
CK_ANN_Pos Each Pos1 and Pos2 value in the Ann table must be a value between 0 and 65535.
CK_AnnRect Each Xpos1, XPos2, YPos1, and YPos2 value in the AnnRect table must be a value between 0 and 65535. XPos1 must be less than XPos2. YPos1 must be less than YPos2.

Unique Constraints

Constraint Name Description
UNQ_Vol_VolumeName Each VolumeName value in the Vol table must be unique.
UNQ_Tstr_TemplName Each TemplateName value in the Tstr table must be unique.
UNQ_Toc Each row in the Toc table must contain a unique combination of ParentId and Name values.
UNQ_Users_UserName A user name must be a unique value.
UNQ_ElecType Each row in the ElecType table must contain a unique combination of Extension and MimeType values.
UNQ_Doc Each row in the Doc table must contain a unique combination of TocId and PageNum values.
UNQ_TemplId_Pos Each row in the TFields table must contain a unique combination of TemplateId and Pos values.
UNQ_TFields_Name Each row in the TFields table must contain a unique combination of TemplateId and Name values.
UNQ_Stamp_StampName Each StampName value in the Stamp table must be unique.

Primary Key Constraints

Constraint Name Description
PK_Vol Each VolumeID value in the Vol table must be unique.
PK_Tstr Each TemplateID value in the Tstr table must be unique.
PK_Toc Each TocId value in the Toc table must be unique.
PK_IndexQueue The EntryTime value of the IndexQueue table must be unique.
PK_Users The UserID values in the Users table must be unique.
PK_GrpList Each GroupId and UserId pair in the GrpList table must be unique.
PK_TrustedLogin Each Sid value in the TrustedLogin table must be unique.
PK_Acl Each row in the Acl table must contain a unique combination of ObjectId, TrusteeId, and Scope values.
PK_ElecType Each ElecTypeId value in the ElecType table must be unique.
PK_Elec Each TocId value in the Elec table must be unique.
PK_Doc Each PageId value in the Doc table must be unique.
PK_TFields Each TFieldId value in the TFields table must be unique.
PK_Lup Each row in the Lup table must contain a unique combination of TFieldId and ItemIndex values.
PK_Wof Each TocId value in the Wof table must be unique.
PK_Lft Each PageId value in the Lft table must be unique.
PK_Loc Each PageId value in the Loc table must be a unique.
PK_Stamp Each StampId value in the Stamp table must be unique.
PK_Ann Each row in the Ann table must contain a unique combination of PageId and ItemId values.
PK_Exc Each row in the Exc table must contain a unique combination of PageId and AnnotationId values.

Foreign Key Constraints

Constraint Name Description
FK_TOC_ParentId Each parent ID must reference a valid Toc ID.
FK_Toc_Vol A volume must reference a valid Volume ID from the VOL .
FK_IndexQueue_Toc All IDs specified in the IndexQueue table must reference a valid TOC ID value.
FK_GrpList_Users Each GroupId value in the GrpList table must reference a valid UserId value from the Users table.
FK_TrustedLogin Each UserId value in the TrustedLogin table must reference a valid UserId value from the Users table.
FK_Acl_Record Each ObjectId value in the Acl table must reference a valid TocId value from the Toc table.
FK_Acl_Users Each TrusteeId value in the Acl table must reference a valid UserId value from the Users table.
FK_Elec_Toc Each TocId value in the Elec table must reference a valid TocId value in the Toc table.
FK_Elec_ElecType Each ElecTypeId value in the Elec table must reference a valid ElecTypeId value in the ElecType table.
FK_Doc_Toc Each TocId value in the Doc table must reference a valid TocId in the Toc table.
FK_TFields_Tstr Each TemplateId value in the TFields table must reference a valid TemplateId value in the Tstr table.
FK_Lup_TFields Each TFieldId value in the Lup table must reference a valid TFieldId in the TFields table.
FK_Wof_Toc Each TocId value in the Wof table must reference a valid TocId in the Toc table.
FK_Lft_Doc Each PageId value in the Lft table must reference a valid PageId in the Doc table.
FK_Loc_Doc Each PageId value in the Loc table must reference a valid PageId in the Doc table.
FK_Ann_Doc Each PageId value in the Ann table must reference a valid PageId value in the Doc table.
FK_Ann_Stamp Each StampId value in the Ann table must reference a valid StampId value in the Stamp table.
FK_AnnRect_ann Each row in the AnnRect table must specify an existing combination of PageId and ItemId values in the Ann table.
FK_Exc_Ann Each row in the Exc table must specify a combination of PageId and AnnotationId values that match a combination of PageId and ItemID values in a row of the Ann table.