数据库设计 - 防止“房间”重复桌子

Question

大家好，我正在尝试为我的一位私人朋友创建一个数据库，由于我在开发数据库方面缺乏经验，我很难尝试建立我目前正在处理的数据库。本质上，我的问题是我的“房间”表与另一个名为“位置”的表有关联；位置就是您所期望的一切（建筑物 ID、街道地址等），并且 Room 有一个包含建筑物 ID 的外键。我希望我的“房间”表具有基于buildingId的房间号的唯一值。

为了让您有更清晰的想法，我将仅对用于创建这些表的脚本进行 c&p。

CREATE TABLE [dbo].[Location](
 [buildingId] [int] IDENTITY(1,1) NOT NULL,
 [streetAddress] [varchar](50) NOT NULL,
 [postalCode] [varchar](7) NOT NULL,
 [province] [varchar](30) NOT NULL,
 [city] [varchar](30) NOT NULL,
 CONSTRAINT [PK_Location] PRIMARY KEY CLUSTERED 
(
 [buildingId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UN_postalCode] UNIQUE NONCLUSTERED 
(
 [postalCode] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [UN_streetAddress] UNIQUE NONCLUSTERED 
(
 [streetAddress] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]



CREATE TABLE [dbo].[Room](
 [rmId] [int] IDENTITY(1,1) NOT NULL,
 [roomNum] [varchar](10) NOT NULL,
 [floor] [int] NOT NULL,
 [capacity] [int] NOT NULL,
 [permission] [bit] NOT NULL,
 [buildingId] [int] NOT NULL,
 CONSTRAINT [PK_Room_1] PRIMARY KEY CLUSTERED 
(
 [rmId] 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

SET ANSI_PADDING OFF
GO

ALTER TABLE [dbo].[Room]  WITH CHECK ADD  CONSTRAINT [FK_Room_Location] FOREIGN KEY([buildingId])
REFERENCES [dbo].[Location] ([buildingId])
GO

ALTER TABLE [dbo].[Room] CHECK CONSTRAINT [FK_Room_Location]
GO

任何帮助将不胜感激。

谢谢。

Answer 1

表级唯一约束？

ALTER TABLE dbo.Room WITH CHECK ADD
    CONSTRAINT UQ_Room_RoomBuildingLocation UNIQUE (roomNum, buildingId)

这也可以是允许 INCLUDE 列的唯一索引