Uploaded image for project: 'Sakai'
  1. Sakai
  2. SAK-41207

Assignments be explicit about implicit indexes

    XMLWordPrintable

    Details

    • Type: Bug
    • Status: Verified
    • Priority: Major
    • Resolution: Fixed
    • Affects Version/s: 12.5, 19.0, 20.0 [Tentative]
    • Fix Version/s: 19.1, 20.0 [Tentative]
    • Component/s: Assignments
    • Labels:
    • Environment:
      Oracle
    • 19 status:
      Resolved
    • 12 status:
      Won't Fix
    • Conversion Script Required:
      Yes
    • Test Plan:
      Hide

      Please add a Test Plan here.

      Show
      Please add a Test Plan here.

      Description

      MySQL creates an implicit index for foreign keys and is documented here:

      MySQL requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. This index might be silently dropped later, if you create another index that can be used to enforce the foreign key constraint.

      https://dev.mysql.com/doc/refman/5.7/en/create-table-foreign-keys.html

      However Oracle does not do the same so the indexes should be explicit.

      There is no change required for MySQL but Oracle users will need to perform the following changes:

       

      create index UK_hyk73ocki8gwvm3ajf8ls08ac on ASN_ASSIGNMENT_ATTACHMENTS (ASSIGNMENT_ID);
      create index UK_8ewbxsplke3c487h0tjujvtm on ASN_ASSIGNMENT_GROUPS (ASSIGNMENT_ID);
      create index UK_jg017qxc4pv3mdf07c1xpytb8 on ASN_SUBMISSION_ATTACHMENTS (SUBMISSION_ID);
      create index UK_3dou5gsqcya4rwwy99l91fofb on ASN_SUBMISSION_FEEDBACK_ATTACH (SUBMISSION_ID);
      

       

        Gliffy Diagrams

          Attachments

            Activity

              People

              • Assignee:
                ern Earle R Nietzel
                Reporter:
                ern Earle R Nietzel
              • Votes:
                0 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:

                  Git Source Code