How to do unique constraint works with NULL value in MySQL

by

No, MySQL is doing the right thing, according to the SQL-99 specification.

https://mariadb.com/kb/en/sql-99/constraint_type-unique-constraint/

A UNIQUE Constraint makes it impossible to COMMIT any operation that
would cause the unique key to contain any non-null duplicate values.
(Multiple null values are allowed, since the null value is never equal
to anything, even another null value.)

If you use a UNIQUE constraint but don’t want multiple rows with NULL, declare the columns as NOT NULL and prohibit any row from having NULL.

More Related Contents:

  1. Remove duplicate rows in MySQL
  2. Delete all Duplicate Rows except for One in MySQL? [duplicate]
  3. Find duplicate records in MySQL
  4. How to abort INSERT operation in MySql trigger?
  5. MySQL: selecting rows where a column is null
  6. MySQL comparison with null value
  7. How do I add a custom CHECK constraint on a MySQL table?
  8. Unique constraint that allows empty values in MySQL
  9. Prevent auto increment on MySQL duplicate insert
  10. Deleting duplicate rows from a table
  11. How to truncate a foreign key constrained table?
  12. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  13. A constraint to prevent the insert of an empty string in MySQL
  14. MySQL Removing Some Foreign keys
  15. When to use NULL in MySQL tables
  16. How do I get SUM function in MySQL to return ‘0’ if no values are found?
  17. Removing duplicates with unique index
  18. MySQL foreign key to allow NULL?
  19. How to delete duplicate rows from a MySQL table
  20. How do I delete all the duplicate records in a MySQL table without temp tables
  21. Use Composite Primary Key as Foreign Key
  22. How to remove constraints from my MySQL table?
  23. Replace null with 0 in MySQL
  24. MySQL distinction between e and é (e acute) – UNIQUE index
  25. MySQL and Check Constraints
  26. List of Constraints from MySQL Database
  27. MySql Query Replace NULL with Empty String in Select
  28. Count duplicates records in Mysql table?
  29. Why in SQL NULL can’t match with NULL?
  30. Force MySQL to return duplicates from WHERE IN clause without using JOIN/UNION?

Leave a Comment