Uploaded image for project: 'Moodle'
  1. Moodle
  2. MDL-63020

Work-around MS SQL and Oracle incorrect handling of unique indexes on nullable columns

    XMLWordPrintable

    Details

      Description

      The standards-compliant behaviour for unique indexes on nullable columns is that non-null values have to be unique, but you can have as many null values as possible.

      Unfortunately, Microsoft did not do that.

      Fortunately, there is a fairly simple work-around that can be used when defining the index https://dba.stackexchange.com/a/80516):

      CREATE UNIQUE INDEX foo ON dbo.bar(key) WHERE key IS NOT NULL;
      

      Hopefully we can use that in lib/ddl/mssql_sql_generator.php.

      Presumably, we don't currently have any indexes like that in core Moodle, or users would be getting errors, but it would be good to check that assumption by checking the install.xml files. (If there were any, we would need to drop them and re-recreate htem in an upgrade step. Probably only if dbfamily == mssql.)

        Attachments

          Issue Links

            Activity

              People

              • Votes:
                1 Vote for this issue
                Watchers:
                4 Start watching this issue

                Dates

                • Created:
                  Updated:
                  Resolved:
                  Fix Release Date:
                  12/Nov/18