Details
-
Type:
Bug
-
Status:
Closed
-
Priority:
Minor
-
Resolution: Won't Fix
-
Affects Version/s: 1.9
-
Fix Version/s: None
-
Component/s: Database SQL/XMLDB
-
Labels:None
-
Database:Any
-
Affected Branches:MOODLE_19_STABLE
Description
If you attempt to create or edit a course so that the Course Shortname is the same as another course, the change is refused and it gives you a warning. Similarly backup and restore appears to modify the name of courses if the shortname is going to be a duplicate.
However this constraint is not reflected in the database schema which has perfomance ramifications.
Issue Links
| This issue is a clone of: | ||||
| MDL-14084 | Course "idnumber" in settings is not enforced as unique, but treated as such in code |
|
|
|
Activity
Hi David and Howard,
there is a cause for this and it's that the uniqueness cannot be guaranteed because fields can contain empty strings and those break uniqueness.
This is caused by a historic problem in the DB layer for old MySQL versions and since then we have all the varchar(xxx) fields with a default '' instead of using proper NULLs.
So, something like that:
create table test (
id bigint(10) not null primary key auto_increment,
shortname varchar(100) not null default '',
fullname varchar(255) not null default '');
create unique index test_uix on test (shortname);
insert into test (fullname) values ('One fullname');
insert into test (fullname) values ('Another fullname');
simply breaks, with duplicate key found error.
This is more noticeable with the idnumber field (MDL-14084) because it's optional in the course configuration page, but internals are the same for both fields.
Until we switch to proper NULLs in those fields (unique indexes use to ignore NULLs so it's safe to have them in more than one record), we only can make application checks, instead of DB checks.
About performance... well... I would measure it as really minor...
So I'm going to close this as won't fix, due to the reasons above. Feel free to reopen, comment or whatever.
Thanks and ciao 
Re-assigning this for Dave. I think it's more of a policy decision if this should be unique, as there is some potential pain in actually fixing it.