精华内容
下载资源
问答
  • 我们都知道unique约束,限制此字段在数据库表中此字段值唯一 但是如果我们传值,那么他默认为null的话,可以有多个吗? 伟大的毛主席告诉我们:实践是检验真理的唯一标准! 本次测试使用的是数据库时MySQL 5.5 首先...

    我们都知道unique约束,限制此字段在数据库表中此字段值唯一
    但是如果我们不传值,那么他默认为null的话,可以有多个吗?
    伟大的毛主席告诉我们:实践是检验真理的唯一标准!
    本次测试使用的是数据库时MySQL 5.5

    1. 首先创建一张表将username字段设置成unique约束
    CREATE TABLE test_unique (
    	#主键
    	uid INT PRIMARY KEY auto_increment,
    	#用户名,设置默认值为null
    	username VARCHAR (10)DEFAULT NULL ,
    	upassword VARCHAR (10),
    	#添加唯一约束,名字叫unique_username
    	CONSTRAINT unique_username UNIQUE (username)
    )
    
    1. 新增一条数据,这里只是新增了密码,并未新增username
    INSERT INTO test_unique(upassword)VALUES("1");
    

    新增成功

    1. 再插入一条数据,也不插入username列
    	INSERT INTO test_unique(upassword)VALUES("2");
    

    依然插入成功!
    插入成功
    4. 多插入几条
    插入成功
    没有任何问题
    5. 测试插入username
    在这里插入图片描述
    再插入一条重复的
    在这里插入图片描述

    发现重复了,违反唯一约束!

    结论:在mysql中unique约束列可以含有多个null!

    展开全文
  • MYSQL 引擎innodb ,设置了唯一索引的列值 允许 多个NULL值存在。 示例: 字段 userCardNum 添加了唯一索引 ...证实是允许存在的多个NULL值数据的: ...所以多个NULL的存在是违反唯一约束的。 ...

    MYSQL 引擎innodb ,设置了唯一索引的列值  允许 多个NULL值存在。

     

     

    示例:

    字段 userCardNum 添加了唯一索引

     

    证实是允许存在的多个NULL值数据的:

     原由:

    因为这里 NULL 的定义 ,是指 未知值。 所以多个 NULL ,都是未知的,不能说它们是相等的,也不能说是不等,就是未知的。所以多个NULL的存在是不违反唯一约束的。

    展开全文
  • 这时 A,B,C 三列都是允许 NULL 的,唯一约束也是 work 的。 后来由于需求的变化,修改了以前的唯一约束,又多加了一列。(至于为什么加就赘述了)。 ALTER TABLE testTable DROP INDEX IDX_UN_LOAN_PLAN_...
  • CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull ON YourTable(yourcolumn) WHERE yourcolumn IS NOT NULL;

    CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
    ON YourTable(yourcolumn)
    WHERE yourcolumn IS NOT NULL;

    展开全文
  • What you're looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values. In the ...

    SQL Server 2008 +

    You can create a unique index that accept multiple NULLs with a WHERE clause. See the answer below.

    What you're looking for is indeed part of the ANSI standards SQL:92, SQL:1999 and SQL:2003, ie a UNIQUE constraint must disallow duplicate non-NULL values but accept multiple NULL values.

    In the Microsoft world of SQL Server however, a single NULL is allowed but multiple NULLs are not...

    In SQL Server 2008, you can define a unique filtered index based on a predicate that excludes NULLs:

    CREATE UNIQUE NONCLUSTERED INDEX idx_yourcolumn_notnull
    ON YourTable(yourcolumn)
    WHERE yourcolumn IS NOT NULL;

    In earlier versions, you can resort to VIEWS with a NOT NULL predicate to enforce the constraint.


    Prior to SQL Server 2008

    You cannot create a UNIQUE constraint and allow NULLs. You need set a default value of NEWID().

    Update the existing values to NEWID() where NULL before creating the UNIQUE constraint.


    对于2008之前版本sqlserver,建议采用下面方案:

    原文地址:https://www.databasejournal.com/features/mssql/article.php/3711501/UNIQUE-Column-with-multiple-NULL-values.htm

    UNIQUE Column with multiple NULL values

    By Muthusamy Anantha Kumar aka The MAK

    As you know, when you create a UNIQUE constraint on a nullable column, SQL Server allows only one NULL value, thereby maintaining the UNIQUEness. However, there are situations when we need more than one NULL value in the column but still have to maintain uniqueness, ignoring all those NULL values.

    In this article, I am going to illustrate how to maintain uniqueness on a column and also allow multiple NULL values.

    Let us assume that we have a database, MyDB, and we want to create a table that holds social security numbers, as shown below.

    USE [master]
    GO
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
    DROP DATABASE [Employee]
    go
    Create Database [Employee]
    go
    use [Employee]
    go
    
    Create Table Emp
    ([Employee id] int not NULL constraint Emp_pk primary key clustered,
    [First Name] varchar(100) NULL,
    [Last Name] varchar(100)  NULL,
    [Nick Name] varchar(100) NULL,
    [Social Security Number] int NULL)
    go
    

    We know social security number is unique, so let us add UNIQUE constraint to the column [Social Security Number].

    Alter table Emp Add constraint ssn_unique UNIQUE 
     ([Social Security Number] )

    Now let us try to insert some data as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(1,'Robert','Bates','Bob',111213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(2,'Robert','Bates','Bob',121213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(3,'Robert','William','Rob',131213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(4,'Sonia','Keira','Sony',131413426)
    

    As we know, social security number is unique. However, in situations of people entering this country using a work visa, it would take sometime for them to get a social security number. Until that time, the data would be NULL.

    Now let us try to insert one row without Social Security Number as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(5,'Mellisa','Brown','Mel',NULL)
    
    Result
    (1 row(s) affected)
    

    Now let us try to insert another row without Social Security Number as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(6,'Sibey','Chikhs','Ciby',NULL)
    
    Result
    
    Msg 2627, Level 14, State 1, Line 1
    Violation of UNIQUE KEY constraint 'ssn_unique'. 
     Cannot insert duplicate key in object 'dbo.Emp'.
    The statement has been terminated.
    

    This is the normal behaviour of UNIQUE constrain on a NULL column. It allows one row of data with NULL values. However, that is not the behaviour we want for this column. We want the column to accept unique values and also accept multiple NULL values.

    This can be achieved using a computed column and adding a contraint to the computed column instead of on the actual Social Security Number column.

    Now let us recreate the database from the scratch, only this time we will add a computed column as shown below.

    USE [master]
    GO
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
    DROP DATABASE [Employee]
    go
    Create Database [Employee]
    go
    use [Employee]
    go
    
    Create Table Emp
    ([Employee id] int not NULL constraint Emp_pk primary key clustered,
    [First Name] varchar(100) NULL,
    [Last Name] varchar(100)  NULL,
    [Nick Name] varchar(100) NULL,
    [Social Security Number] int NULL)
    go
    
    Alter table Emp Add MySSN as case when [Social Security Number]
     is NULL then [Employee id] else [Social Security Number] end
    go
    

    Now let us add the UNIQUE constraint to the computed column as shown below.

    Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
    go
    

    Now let us try to insert some data as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(1,'Robert','Bates','Bob',111213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(2,'Robert','Bates','Bob',121213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(3,'Robert','William','Rob',131213422)
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(4,'Sonia','Keira','Sony',131413426)
    

    Let’s try to insert one row without Social Security Number as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(5,'Mellisa','Brown','Mel',NULL)
    
    Result
    (1 row(s) affected)
    
    

    Try to insert another row without Social Security Number:

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(6,'Sibey','Chikhs','Ciby',NULL)
    
    Result
    (1 row(s) affected)
    

    If the Social Security Number column is a varchar column then we can create the computed column and the constraint as shown below in order to achieve the same goal of having unqiueness with multiple NULL values.

    USE [master]
    GO
    IF  EXISTS (SELECT name FROM sys.databases WHERE name = N'Employee')
    DROP DATABASE [Employee]
    go
    Create Database [Employee]
    go
    use [Employee]
    go
    
    Create Table Emp
    ([Employee id] int not NULL constraint Emp_pk primary key clustered,
    [First Name] varchar(100) NULL,
    [Last Name] varchar(100)  NULL,
    [Nick Name] varchar(100) NULL,
    [Social Security Number] varchar(12) NULL)
    go
    

    Let’s create the computed column for Social Security Number as shown below.

    Alter table Emp Add MySSN as case when [Social Security Number]
     is NULL then convert(varchar(12),[Employee id]) else [Social Security Number] end
    go
    

    Now let’s add the UNIQUE constraint to the computed column:

    Alter table Emp Add constraint ssn_unique UNIQUE ([MySSN] )
    go

    Let’s try to insert some data as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(1,'Robert','Bates','Bob','111-21-3422')
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(2,'Robert','Bates','Bob','121-21-3422')
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(3,'Robert','William','Rob','131-21-3422')
    
    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(4,'Sonia','Keira','Sony','131-41-3426')
    

    Now try to insert one row without Social Security Number:

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(5,'Mellisa','Brown','Mel',NULL)
    
    Result
    (1 row(s) affected)
    

    Now let’s try to insert another row without Social Security Number as shown below.

    Insert into Emp ([Employee id],[First Name],[Last Name],
    [Nick Name],[Social Security Number])
    values(6,'Sibey','Chikhs','Ciby',NULL)
    
    Result
    (1 row(s) affected)
    

    Conclusion

    We have created a column on a table, which holds UNIQUE values and also multiple NULL values using Primary key values, computed column and UNIQUE constraint on the computed column.


    展开全文
  • Oracle唯一约束NULL处理

    千次阅读 2018-11-25 18:22:09
    根据NULL的定义,NULL表示...根据这个定义,多个NULL值的存在应该违反唯一约束。   CREATE TABLE TESTAA (ID NUMBER) SELECT * FROM TESTAA; ALTER TABLE TESTAA ADD UNIQUE (ID); INSERT INTO TESTAA VALU...
  • 今天正在吃饭,一个朋友提出了一个他面试中遇到的问题,MySQL允许在唯一索引字段中添加多个NULL值。 这个问题对于我一个非专业DBA来说,也没特地去验证过,所以正好借此机会验证一下,做个记录: 测试环境: ...
  • 唯一约束可以确保一列或者几列出现重复。 非空约束 MySQL非空约束(NOT NULL)可以通过 CREATE TABLE 或 ALTER TABLE 语句实现。在表中某个列的定义后加上关键字 NOT NULL 作为限定词,来约束该列的取值不能为...
  • MySQL中有唯一约束的列能否为null

    千次阅读 2017-01-16 14:55:11
      唯一索引中允许有null.   参考链接        
  • 首先说明一点,主键又称主键约束,...·主键字段值不能NULL唯一约束字段值可以为NULL; ·主键字段可以做为其他表的外键,唯一约束字段不可以做为其他表的外键; ·SQLServer默认为主键字段创建聚集索引,为唯一约
  • 文章目录准备知识定义唯一约束使用SSMS工具定义唯一约束使用SQL方式定义唯一约束方式一:在创建数据表的时候定义唯一约束方式二:修改数据表定义唯一约束删除唯一约束使用SSMS工具删除唯一约束方式一:在对象资源...
  • MySQL约束(主键,唯一,非空,外键) ...非空约束是为了让数据在存入数据时,保证值不null。 关键字:not null 1. 创建表时添加约束 CREATE TABLE stu( id INT, NAME VARCHAR(20) NOT NUL
  • NULL唯一约束

    万次阅读 2014-04-08 19:59:03
    NULL是个很特殊的东西,也容易引起一些问题。参看:http://blog.csdn.net/zedware/article/details/20488167。
  • NULL唯一约束UNIQUE的对应关系

    千次阅读 2017-04-28 11:06:22
    NULL唯一约束UNIQUE的对应关系
  • 1、为department的manager添加唯一约束,保证manager列中出现重复(unique_manager是随便设置的约束名称) alter table department add constraint unique_manager unique(manager); 关于唯一约束的各种...
  • 数据库MySQL之主键约束、唯一约束是什么? 文章目录1. 主键约束2. 唯一约束2.1 UNIQUE KEY 1. 主键约束 主键可以写为PRIMARY KEY,也可以写成KEY 每张数据表只能存在一个主键 主键保证记录的唯一性 主键自动为NOT ...
  • NOT NULL 约束强制列接受 NULL 值(NULL值就是没有值或缺值)。NOT NULL 约束强制字段始终包含值,即向字段添加值,就无法插入新记录或者更新记录,也就是在插入或更新行时该列必须有值。 注意:NULL值时没有值...
  • 同一具有UNIQUE约束的列或者行,可以允许...它来定义基本表的主键,起唯一标识作用,值不能NULL,不重复,以此来保证实体的完整性。   黑色头发 http://heisetoufa.iteye.com 如果发现本文有误,欢迎批评指正...
  • 更好的实现是,修改点赞表的user_id和comment_id为唯一约束,即这两列不能同时相同,这样在执行插入操作的话,如果已经点过赞了,数据库会抛出违反了唯一键约束,这样的话,就可以避免多一次数据库查询操作了.具体设置多...
  • 主键约束和唯一约束

    千次阅读 2019-03-07 21:30:59
    主键约束和唯一约束主键约束和唯一约束的区别普通索引和唯一索引Mysql中的索引普通索引(非...不同之处在于主键约束的索引键(唯一索引)在定义上允许为NULL,而唯一约束的索引键(唯一索引)在定义上允许为NULL; ...
  • 不然会一直报错 duplicate entry '' for key ‘xxoo’
  • 唯一约束和唯一索引区别

    千次阅读 2017-09-21 21:02:55
    3) 主键列上没有任何两行具有相同(即重复),允许空(NULL).4) 主健可作外健,唯一索引可; 2.唯一约束(UNIQUE) 1) 唯一约束用来限制受主键约束的列上的数据的唯一性,用于作为访问...
  • NULL UNIQUE唯一约束

    2016-12-14 11:24:52
    根据NULL的定义,NULL表示的是...根据这个定义,多个NULL值的存在应该违反唯一约束。   CREATE TABLE TESTAA (ID NUMBER) SELECT * FROM TESTAA; ALTER TABLE TESTAA ADD UNIQUE (ID); INSERT INTO T
  • 主键约束和唯一约束的区别

    千次阅读 2017-11-14 09:30:26
    3) 主键列上没有任何两行具有相同(即重复),允许空(NULL). 4) 主健可作外健,唯一索引可; 2.唯一约束(UNIQUE) 1) 唯一约束用来限制受主键约束的列上的数据的唯一性,用于作为访问某行...
  • 什么是约束? 数据库—由若干张数据表组成(理解为一个类库) 数据表—由行和列组成的一个二维数组(理解为一个类) 列—字段(理解为表中对象的一个属性) 行—理解为表中的一个对象 约束是数据库中保证数据完整性...
  • 谈谈唯一约束和唯一索引

    万次阅读 多人点赞 2018-03-27 21:20:00
    最近在看数据库相关知识,感觉唯一约束和唯一索引好像有点类似,于是研究了一番,于是就有了这篇文章。 概念 开始之前,先解释一下约束和索引。 约束 全称完整性约束,它是关系数据库中的对象,用来存放插入到...

空空如也

空空如也

1 2 3 4 5 ... 20
收藏数 101,429
精华内容 40,571
关键字:

唯一约束的值不能是null值