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

              timhunt Tim Hunt
              timhunt Tim Hunt
              John Beedell John Beedell
              Eloy Lafuente (stronk7) Eloy Lafuente (stronk7)
              CiBoT CiBoT
              David Woloszyn, Huong Nguyen, Jake Dallimore, Meirza, Michael Hawkins, Raquel Ortega, Safat Shahin, Stevani Andolo
              Votes:
              1 Vote for this issue
              Watchers:
              4 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved:
                12/Nov/18