CREATE FUNCTION f_check(@a VARCHAR(10)) RETURNS BIT AS BEGIN DECLARE @bit BIT SELECT @bit = 0 WHERE (SELECT COUNT(1) FROM tb WHERE a = @a) = 1 OR @a IS NULL
SET @bit = ISNULL(@bit,1) RETURN @bit END GO CREATE TABLE tb(a VARCHAR(10) NULL,CONSTRAINT ck_tb CHECK (dbo.f_check(a) = 0)) GO INSERT tb SELECT 'cc' UNION ALL SELECT NULL GO INSERT tb SELECT 'cc' GO INSERT tb SELECT NULL GO SELECT * FROM tb GO DROP TABLE tb DROP FUNCTION dbo.f_check /* (2 row(s) affected) Msg 547, Level 16, State 0, Line 1 The INSERT statement conflicted with the CHECK constraint "ck_tb". The conflict occurred in database "WebReport", table "dbo.tb", column 'a'. The statement has been terminated.(1 row(s) affected) a ---------- cc NULL NULL(3 row(s) affected) */
--尽管 UNIQUE 约束和 PRIMARY KEY 约束都强制唯一性,
--但想要强制一列或多列组合(不是主键)的唯一性时
--应使用 UNIQUE 约束而不是 PRIMARY KEY 约束。 --可以对一个表定义多个 UNIQUE 约束,但只能定义一个 PRIMARY KEY 约束。--而且,UNIQUE 约束允许 NULL 值,这一点与 PRIMARY KEY 约束不同。
--不过,当与参与 UNIQUE 约束的任何值一起使用时,每列只允许一个空值。
[myid] [char] (10) ,
[mytext] [char] (10)
)ALTER TABLE [tb3] ADD
CONSTRAINT [wy] UNIQUE NONCLUSTERED
(
[myid]
) insert into tb3
select null,'adsfad' union all
select 'aaaa','adsfad' union all
select 'bbbb','adsfad' union all
select 'cccc','ccccd'select *
from tb3
/*
myid mytext
---------- ----------
NULL adsfad
aaaa adsfad
bbbb adsfad
cccc ccccd (所影响的行数为 4 行)
*/
(2)目前我是前台SQL语句程序控制,但是发现在1,2万数据中,总有一两个数据会重复。
可能原因是:多台客户端操作SQLServer时,服务器相应未能及时响应、更新。所以不知道各位是否还有好的建议。
CREATE FUNCTION f_check(@a VARCHAR(10))
RETURNS BIT
AS
BEGIN
DECLARE @bit BIT
SELECT @bit = 0
WHERE (SELECT COUNT(1) FROM tb WHERE a = @a) = 1
OR @a IS NULL
SET @bit = ISNULL(@bit,1)
RETURN @bit
END
GO
CREATE TABLE tb(a VARCHAR(10) NULL,CONSTRAINT ck_tb CHECK (dbo.f_check(a) = 0))
GO
INSERT tb SELECT 'cc' UNION ALL SELECT NULL
GO
INSERT tb SELECT 'cc'
GO
INSERT tb SELECT NULL
GO
SELECT * FROM tb
GO
DROP TABLE tb
DROP FUNCTION dbo.f_check
/*
(2 row(s) affected)
Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the CHECK constraint "ck_tb". The conflict occurred in database "WebReport", table "dbo.tb", column 'a'.
The statement has been terminated.(1 row(s) affected)
a
----------
cc
NULL
NULL(3 row(s) affected)
*/
感谢感谢