SQL Server 2008有没有像MySQL的enum
数据类型一样的东西?
SQL Server 2008有没有像MySQL的enum
数据类型一样的东西?
它没有。但是有一个模糊的等效物:
mycol VARCHAR(10) NOT NULL CHECK (mycol IN('Useful', 'Useless', 'Unknown'))
我在这方面找到的最佳解决方案是创建一个查找表,以可能的值作为主键,并创建一个指向查找表的外键。
CREATE FUNCTION ActionState_Preassigned()
RETURNS tinyint
AS
BEGIN
RETURN 0
END
GO
CREATE FUNCTION ActionState_Unassigned()
RETURNS tinyint
AS
BEGIN
RETURN 1
END
-- etc...
针对这个问题,可能最好的解决方案是一个简单的查找表(什么是查找表?)。尽管如此,您可以实现类似以下内容:
User.Role
是外键,而 Role.Type
是它所引用的主键。在那张表中,您将拥有以下值:
nvarchar(15)
Role
表中提供的不同的值到 User.Role
,则会出现错误。CREATE TABLE [dbo].[User](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Username] [nvarchar](50) NOT NULL,
[Email] [nvarchar](75) NOT NULL,
[Password] [nvarchar](25) NOT NULL,
[Role] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[User] WITH CHECK ADD CONSTRAINT [FK_User_Role] FOREIGN KEY([Role])
REFERENCES [dbo].[Role] ([Type])
GO
ALTER TABLE [dbo].[User] CHECK CONSTRAINT [FK_User_Role]
GO
CREATE TABLE [dbo].[Role](
[Type] [nvarchar](15) NOT NULL,
CONSTRAINT [PK_Role] PRIMARY KEY CLUSTERED
(
[Type] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
您可以实现一个类似于这样的查找表:在SQL Server中创建枚举
ALTER TABLE dbo.yourTable
ADD CONSTRAINT yourColumn CHECK(yourColumn IN('XL','L','M','S','XS'))