SQL Server表约束设计(SQL Server Table constraints design)

SQL Server表约束设计(SQL Server Table constraints design)

我有一个SQL服务器表的设计问题。 我在桌子上有设备如下：

Table Equipments id int PK idCommunications int FK to table Communications (defined below) Name varchar

每个设备只能有一种通信系统（表通信）。

Table Communications id int PK Name varchar

每个通信系统可以具有任意数量的参数。 我不想为每个我知道的通信系统创建一个特定的表，因此我创建了一个列出参数的表

Table ParamCommunications id int PK idCommunications int FK to table Communications Parameter varchar

和另一个表，其中包含每个设备的每个参数的值

Table ConfComEquipment id int PK idParamCommunications int FK to table ParamCommunications idEquipment int FK to table Equipment Value varchar

该表结构允许我为每个通信系统创建参数列表并将它们分配给一个设备。 我发现的问题是，在最后一个表中，我可以输入idParamCommunications的配置，该配置不属于设备定义的相同idCommunications。

例如。 我有两个通讯系统

id Name 1 Serial 2 TCP/IP

我在ParamCommunications表中有以下参数：

id idCommunications Parameter 1 1 BaudRate 2 1 COMPort 3 2 IPAddress

表设备有：

id idCommunications Name 1 1 SerialEquipment 2 2 EthernetEquipment

当我对表进行配置时，约束将不允许我使用任何未定义的设备或未定义的参数，但允许我这样做：

id idCommunications idParamCommunications idEquipment Value 1 1 1 2 "somevalue"

id = 2的设备具有idCommunications = 2，但在此设置中不是约束。

我知道我有以下潜在的解决方案：

创建使用设备和通信来定义它的复合主键。 这将给我带来问题，我将需要使用复合链接到一些不关心通信系统的其他表（此处未显示）。 创建另一个表：CommunicationByEquipment，它将设备链接到其通信系统，然后从ConfComEquipment表中引用该表的PK。 控制程序端的数据完整性。 可能更容易出错和孤儿记录

还有其他选择我看不到吗？ 如果没有，你会推荐哪一个？

感谢您的帮助，感谢抱歉。 我打算粘贴显示表格结构的数字，但直到我获得更高的排名才允许我这样做。

生疏

I have a design question for SQL server tables. I have equipments in a table as follows:

Table Equipments id int PK idCommunications int FK to table Communications (defined below) Name varchar

Each equipment can have only one type of communications system (table Communications).

Table Communications id int PK Name varchar

Each communication system can have an arbitrary number of parameters. I don't want to create an specific table for each communications system I know as they evolve so I created a table that list the parameters

Table ParamCommunications id int PK idCommunications int FK to table Communications Parameter varchar

and another table that contains the value of each parameter for each equipment

Table ConfComEquipment id int PK idParamCommunications int FK to table ParamCommunications idEquipment int FK to table Equipment Value varchar

This table structure allows me to create the list of parameters for each communication system and assign them to one equipment. The problem I found is that in the last table I can enter a configuration for an idParamCommunications that does not belong to the same idCommunications that the equipment gets defined.

For example. I have two communications systems

id Name 1 Serial 2 TCP/IP

and I have the following parameters in table ParamCommunications:

id idCommunications Parameter 1 1 BaudRate 2 1 COMPort 3 2 IPAddress

The table Equipment have:

id idCommunications Name 1 1 SerialEquipment 2 2 EthernetEquipment

When I do the config the table the constraints won't allow me to use any undefined equipment or undefined parameter but will allow me to do this:

id idCommunications idParamCommunications idEquipment Value 1 1 1 2 "somevalue"

Equipment with id=2 has an idCommunications = 2 but in this setup that is not a constraint.

I understand I have the following potential solutions:

Create a composite primary key that uses the equipment and the communication to define it. This will create the problem to me that I will need to use the composite to link to some other tables (not shown here) that don't care about the communication system. Create another table: CommunicationByEquipment that will link the equipment to its communications system and then refer to the PK of this table from the ConfComEquipment table. Control the data integrity in the program side. Probably more prone to error and orphan records

Is there another option I don't see? If not, which one would you recommend?

Thanks for your help and sorry for the long post. I was going to paste figures showing the table structure but I'm not allowed until I get a higher rank.

Rusty

最满意答案

主键不是外键目标的唯一可能 - 任何唯一索引也可以由外键约束作为目标。 所以，我认为以下结构强制执行您的要求：

create table Communications ( ID int not null, Name varchar(20) not null, constraint PK_Communications PRIMARY KEY (ID), constraint UQ_Communication_Names UNIQUE (Name) ) go create table CommunicationParameters ( ID int not null, CommunicationID int not null, Parameter varchar(20) not null, constraint PK_CommunicationParameters PRIMARY KEY (ID), constraint UQ_CommunicationParameter_Parameters UNIQUE (Parameter), constraint FK_CommunicationParameters_Communications FOREIGN KEY (CommunicationID) references Communications (ID), constraint UQ_CommunicationParameters_Communication_XRef UNIQUE (ID,CommunicationID) ) go create table Equipments ( ID int not null, CommunicationID int not null, Name varchar(20) not null, constraint PK_Equipments PRIMARY KEY (ID), constraint UQ_Equipment_Names UNIQUE (Name), constraint FK_Equipments_Communications FOREIGN KEY (CommunicationID) references Communications (ID), constraint UQ_Equipment_Communication_XRef UNIQUE (ID,CommunicationID) )

最后：

create table ConfComEquipment ( ID int not null, CommunicationID int not null, CommunicationsParameterID int not null, EquipmentID int not null, Value varchar(99) not null, constraint PK_ConfComEquipment PRIMARY KEY (ID), constraint FK_ConfComEquipment_CommunicationParameters FOREIGN KEY (CommunicationsParameterID) references CommunicationParameters (ID), constraint FK_ConfComEquipment_Equipment FOREIGN KEY (EquipmentID) references Equipments (ID), constraint FK_ConfComEquipment_CommunicationParameters_XRef FOREIGN KEY (CommunicationsParameterID,CommunicationID) references CommunicationParameters (ID,CommunicationID), constraint FK_ConfComEquipment_Equipment_XRef FOREIGN KEY (EquipmentID,CommunicationID) references Equipments (ID,CommunicationID) )

请注意，在名称末尾添加了XRef这些约束，允许强制执行约束，即最后一个表中的CommunicationID列与存储在CommunicationParameters和Equipments表中的值一致，同时保留主键在这些表上只作为ID列。

严格来说，这现在使“真正的”外键， FK_ConfComEquipment_CommunicationParameters和FK_ConfComEquipment_Equipment成为冗余， XRef约束就足够了。 您是否可以将这些仍保留在最终的桌面设计中，这是可选的。

Primary keys aren't the only possibility for the target of a foreign key - any unique index can also be targeted by a foreign key constraint. So, I think the below structure enforces your requirements:

create table Communications ( ID int not null, Name varchar(20) not null, constraint PK_Communications PRIMARY KEY (ID), constraint UQ_Communication_Names UNIQUE (Name) ) go create table CommunicationParameters ( ID int not null, CommunicationID int not null, Parameter varchar(20) not null, constraint PK_CommunicationParameters PRIMARY KEY (ID), constraint UQ_CommunicationParameter_Parameters UNIQUE (Parameter), constraint FK_CommunicationParameters_Communications FOREIGN KEY (CommunicationID) references Communications (ID), constraint UQ_CommunicationParameters_Communication_XRef UNIQUE (ID,CommunicationID) ) go create table Equipments ( ID int not null, CommunicationID int not null, Name varchar(20) not null, constraint PK_Equipments PRIMARY KEY (ID), constraint UQ_Equipment_Names UNIQUE (Name), constraint FK_Equipments_Communications FOREIGN KEY (CommunicationID) references Communications (ID), constraint UQ_Equipment_Communication_XRef UNIQUE (ID,CommunicationID) )

And finally:

create table ConfComEquipment ( ID int not null, CommunicationID int not null, CommunicationsParameterID int not null, EquipmentID int not null, Value varchar(99) not null, constraint PK_ConfComEquipment PRIMARY KEY (ID), constraint FK_ConfComEquipment_CommunicationParameters FOREIGN KEY (CommunicationsParameterID) references CommunicationParameters (ID), constraint FK_ConfComEquipment_Equipment FOREIGN KEY (EquipmentID) references Equipments (ID), constraint FK_ConfComEquipment_CommunicationParameters_XRef FOREIGN KEY (CommunicationsParameterID,CommunicationID) references CommunicationParameters (ID,CommunicationID), constraint FK_ConfComEquipment_Equipment_XRef FOREIGN KEY (EquipmentID,CommunicationID) references Equipments (ID,CommunicationID) )

Note that it is the addition of these constraints with XRef at the end of their names that allows is to enforce the constraint that the CommunicationID column in this last table agrees with the values stored in both the CommunicationParameters and Equipments tables, whilst leaving the primary keys on these tables as just the ID column.

Strictly, this now makes the "real" foreign keys, FK_ConfComEquipment_CommunicationParameters and FK_ConfComEquipment_Equipment as redundant, the XRef constraints are sufficient. It's optional whether you keep these still in your final table design.