CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
)
) ON [PRIMARY]
GO
我如何添加一个唯一的约束列fcode, scode, dcode与t-sql和/或管理工作室?Fcode, scode, dcode必须是唯一的。
当前回答
USE [TSQL2012]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[seq] [bigint] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[cat] [nvarchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED
(
[name] ASC,
[cat] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
其他回答
这也可以在GUI中完成。下面是一个向现有表添加多列唯一约束的示例。
在表下，右键单击“索引”->单击/悬停“新建索引”->单击“非聚集索引”…
将给出一个默认的索引名称，但您可能希望更改它。选中“惟一”复选框，然后单击“添加…”按钮
检查您希望包含的列
在每个窗口中单击OK，就完成了。
通过在表创建时使用约束定义，可以指定一个或多个横跨多列的约束。从technet的文档中简化的语法形式为:
CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ]
(
column [ ASC | DESC ] [ ,...n ]
)
因此，结果表定义将是:
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
),
CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED
(
[fcode], [scode], [dcode]
)
) ON [PRIMARY]
USE [TSQL2012]
GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table_1](
[seq] [bigint] IDENTITY(1,1) NOT NULL,
[ID] [int] NOT NULL,
[name] [nvarchar](50) NULL,
[cat] [nvarchar](50) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED
(
[name] ASC,
[cat] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
如果这个表已经在数据库中创建了，那么你可以稍后通过使用下面的SQL查询添加一个唯一的约束:
ALTER TABLE dbo.User
ADD CONSTRAINT ucCodes UNIQUE (fcode, scode, dcode)
