if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Contact_User]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Contact] DROP CONSTRAINT FK_Contact_User GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Contact_User1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[Contact] DROP CONSTRAINT FK_Contact_User1 GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TimeLapse_User]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1) ALTER TABLE [dbo].[TimeLapse] DROP CONSTRAINT FK_TimeLapse_User GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[User] GOCREATE TABLE [dbo].[User] ( [UserID] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Login] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [DateOfBirth] [datetime] NULL , [PhoneNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [CellNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Address] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL , [Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL , [IsAdministrator] [bit] NOT NULL ) ON [PRIMARY] GO
ALTER TABLE [dbo].[User] WITH NOCHECK ADD CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED ( [UserID] ) ON [PRIMARY] , CONSTRAINT [IX_Login] UNIQUE NONCLUSTERED ( [Login] ) ON [PRIMARY] GO
谢谢brightheroes(闭关|那一剑的风情) ,应该清楚了!揭贴给分!
insert into [user](UserID,Login,Password,FirstName,LastName,DateOfBirth,PhoneNumber,CellNumber,Address,Email,IsAdministrator) select '21','jason','1gh3','j1i','h1h',null,'123456','1','s1f','sd1f',0insert into [user](UserID,Login,Password,FirstName,LastName,DateOfBirth,PhoneNumber,CellNumber,Address,Email,IsAdministrator) select '21','jason','1gh23','j1in','h1h',null,'123456','1','s1f','sd1f',0这两个一执行, 违反了 UNIQUE KEY 约束 'IX_Login'。不能在对象 'User' 中插入重复键。 语句已终止。
但是对于其他字段的内容是否唯一,还是需要你用代码去判断的,与是否用GUID无关
主键是主键
用GUID做主键的好处在于
插入之前就可以得到插入之后的ID
至于你的某字段是否重复,还是要自己做判断
你说的那个程序里面没有判断
那肯定是在数据库的那一个列上做了一个Check
物理唯一性是什么意思?能说说吗?
有这样一个例子:
表user有以下几个字段:
userid char 主键(使用GUID)
Login char (用户名)
Password char
FirstName char
LastName char
然后在增加用户的时候的代码只有:
try
{
cmd.ExecuteNonQuery()
}
catch
{
//处理异常
}
finally
{
//关闭数据库连接
}
其它代码中并没有对用户的唯一性做任何判断,在增加不同的用户正常,但一旦增加已经存在的FirstName且LastName的时候就抛出异常,这里的主键是USERID。不知道这里的的唯一性是怎么判断的
其它字段有重复吧?
我也查看了这个表的CHECK约束,也没有任何的约束,不知道是怎么弄的,真是有点糊涂了
大家看看让sqlserver自动生成一个·
如果是存放的登陆名称
那么他是一个唯一的索引
是不允许重复的
插入相同的用户名称的时候,就会有异常
捕获住,就可以提示说,用户重复了
ALTER TABLE [dbo].[Contact] DROP CONSTRAINT FK_Contact_User
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_Contact_User1]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[Contact] DROP CONSTRAINT FK_Contact_User1
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_TimeLapse_User]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[TimeLapse] DROP CONSTRAINT FK_TimeLapse_User
GOif exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[User]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[User]
GOCREATE TABLE [dbo].[User] (
[UserID] [char] (36) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Login] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[Password] [varchar] (15) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[FirstName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[LastName] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[DateOfBirth] [datetime] NULL ,
[PhoneNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[CellNumber] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Address] [varchar] (300) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Email] [varchar] (100) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL ,
[IsAdministrator] [bit] NOT NULL
) ON [PRIMARY]
GO
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[UserID]
) ON [PRIMARY] ,
CONSTRAINT [IX_Login] UNIQUE NONCLUSTERED
(
[Login]
) ON [PRIMARY]
GO
select '21','jason','1gh3','j1i','h1h',null,'123456','1','s1f','sd1f',0insert into [user](UserID,Login,Password,FirstName,LastName,DateOfBirth,PhoneNumber,CellNumber,Address,Email,IsAdministrator)
select '21','jason','1gh23','j1in','h1h',null,'123456','1','s1f','sd1f',0这两个一执行,
违反了 UNIQUE KEY 约束 'IX_Login'。不能在对象 'User' 中插入重复键。
语句已终止。