【sqlserver】数据违反唯一约束/非空约束 插入报错规则测试

CREATE TABLE testbzm(
[INNID] bigint  NOT NULL  ,
[NAME] varchar(50) NOT NULL  ,
[URL] varchar(100) NOT NULL
)   
ALTER TABLE testbzm ADD CONSTRAINT testbzm$BPK_AK_Key UNIQUE NONCLUSTERED(INNID,NAME) WITH(ONLINE=ON,FillFactor=90)

insert into testbzm (INNID,name,url) values(1,'HELLO','www')

--同时出现
insert into testbzm (INNID,name,url) values(1,'HELLO',NULL)
--Cannot insert the value NULL into column 'URL', table 'njtestdb.dbo.testbzm'; column does not allow nulls. INSERT fails.

--先出现违反空约束
insert into testbzm (INNID,name,url) values(2,'TEMP','www'),(3,'TEST',NULL),(1,'HELLO','www') 
--Cannot insert the value NULL into column 'URL', table 'njtestdb.dbo.testbzm'; column does not allow nulls. INSERT fails.

--先出现违反唯一约束
insert into testbzm (INNID,name,url) values(2,'TEMP','www'),(1,'HELLO','www'),(3,'TEST',NULL)
--Violation of UNIQUE KEY constraint 'testbzm$BPK_AK_Key'. Cannot insert duplicate key in object 'dbo.testbzm'. The duplicate key value is (1, HELLO).

drop table testbzm

/*
结论：
按顺序插入，先遇到违反对应约束的行报对应的错。若一行同时违反唯一约束和非空约束，先报非空约束的错误。
*/