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

              Assignee:
              timhunt Tim Hunt
              Reporter:
              timhunt Tim Hunt
              Peer reviewer:
              John Beedell
              Integrator:
              Eloy Lafuente (stronk7)
              Tester:
              CiBoT
              Participants:
              Component watchers:
              Andrew Nicols, Jun Pataleta, Michael Hawkins, Shamim Rezaie, Simey Lameze
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

                Dates

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