Mysql,sql Server,oracle does the unique index field allow multiple null values?

Source: Internet
Author: User

A recent project involving SQL Server 2008, because of business requirements, wanted to establish a unique index, but found that in SQL Server, a unique index field cannot have multiple null values, and the following is an error message:

CREATE UNIQUE nonclustered INDEX  on  'dbo. Users'weixin_openid_ui'CREATEUNIQUE   The INDEX statement terminates. The duplicate key value is (<NULL>).

But theoretically, null and null values are not equal, and why can't they appear in a unique index field?

The following is a test of the MySQL InnoDB engine:

As you can see, in MySQL's InnoDB engine, there are multiple null values that are allowed in a unique indexed field. According to the null definition, NULL represents an unknown, so the result of two null comparisons is neither equal nor unequal, and the result is still unknown. According to this definition, the existence of multiple null values should not violate the unique constraint, so it is reasonable . SQL Server is a bit unreasonable and does not know if Oracle allows multiple null values to appear automatically in a unique index? There is no Oracle environment and cannot be tested. Search, and found that Oracle and MySQL are consistent, is logically self-consistent . This is also more in line with the actual business scenario.

Handling of NULL in Oracle UNIQUE constraints

Http://database.ctocio.com.cn/tips/427/8292927.shtml

One important difference between a unique index and a primary key in Oracle and MySQL is that the unique index allows NULL, and the primary key does not allow null.

Mysql,sql Server,oracle does the unique index field allow multiple null values?

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.