Unique Key Constraint Issue - MySQL, SQL Server, Oracle, Postgres

Question

I have a employee table which has 10 columns and have to create a unique key constraint for id, name, address, mobile

In the above case address might can come as null and mobile can come as null. However when they comes the uniqueness should be maintained.

first, i created a unique constraint by combining all the above keys and following is observed.

Actual behaviour in MySQL.

   001-Thiagu-NULL-900000 - Accepted
   001-Thiagu-NULL-900000 - Accepted
   001-Thiagu-0001-900000 - Accepted
   001-Thiagu-0001-900000 - Rejected - Duplicate Record

Expected behaviour in all the databases

   001-Thiagu-NULL-900000 - Accepted
   001-Thiagu-NULL-900000 - Rejected - Duplicate Record
   001-Thiagu-0001-900000 - Accepted
   001-Thiagu-0001-900000 - Rejected - Duplicate Record

Basically the similar should be considered for duplication no matter whether the value exist as NULL or Not.

To overcome this problem i dropped the idea of combining and creating unique by adding columns to the unique constraint and come up with a new column of string type with unique constraint.

One each insert of the record i manually construct and give the value on any insert so that uniqueness will be maintained.

Is that would be the right approach or any other way to fix in the above first approach which i am not sure.

The created constraint should work for MySQL, SQL Server, Oracle and Postgres.

Please [edit] your question (by clicking on the [edit] link below it) and add the CREATE TABLE statement for the table in question include all indexes and constraints. [Formatted text](https://meta.stackoverflow.com/a/251362) please, [no screen shots](http://meta.stackoverflow.com/questions/285551/why-may-i-not-upload-images-of-code-on-so-when-asking-a-question/285557#285557). [edit] your question do **not** post code in comments. — , Dec 12 '18 at 15:00
If you type "sql null in unique key" in the search box, then you get a list with a LOT of entries probably answering your question already : https://stackoverflow.com/search?q=sql+null+in+unique+key . The thing I'd want to say myself is also in the list, at https://stackoverflow.com/questions/3906811/null-permitted-in-primary-key-why-and-in-which-dbms . — Erwin Smout, Dec 12 '18 at 15:15
yes, looking for a solution that works across all the four mentioned database products — Java Developer, Dec 12 '18 at 15:56

score 2 · Answer 1 · answered Dec 13 '18 at 10:10

In SQL, null never equals null. That's not a bug, that's a feature. NULL IS NOT DISTINCT FROM NULL is true, but key declarations employ '=' [in the equivalent longhands], not IS NOT DISTINCT FROM. The 'key' constraint that you want should employ IS NOT DISTINCT FROM, therefore you cannot get there by declaring keys.

The next option would be a CHECK constraint, but products are unlikely to support CHECK constraints accessing other rows than the one being inserted.

The next option would be to create an ASSERTION, but no product supports that [reliably], essentially for the same reason as why they don't support cross-row CHECK constraints.

The next option is to enforce this in a stored procedure, but then you're likely to bump into [some of] the products only talking their proprietary dialect of SQL/PSM language.

The next option is application code.

Unique Key Constraint Issue - MySQL, SQL Server, Oracle, Postgres

1 Answers1