I have an unusual situation to model in a MS SQL Server database: the primary key of a table is a multi-segment 'natural' key composed of 5 foreign keys (of fixed sizes).
我有一个不寻常的情况在MS SQL Server数据库中建模:表的主键是由5个外键(固定大小)组成的多段“自然”键。
I'd like to be able to define a user-defined data type to implement the data structure based on a CHAR(8) primitive in such a way that the elements are addressable as individual fields.
我希望能够定义用户定义的数据类型,以基于CHAR(8)原语实现数据结构,使得元素可作为单个字段寻址。
For example (in bad pseudocode):
例如(在错误的伪代码中):
UDT seggy
(
seg1 char(2),
seg2 char(1),
seg3 char(1),
seg4 char(2),
seg5 char(2)
)
create table yotable
(
pkfield seggy NOT NULL,
etc varchar(14), --whatever etc.
)
with pkfield as the primary key,
and also with seg1 as a foreign key to tableseg1,
and also with seg2 as a foreign key to tableseg2,
and so on
and then be able to do things like this:
然后能够做这样的事情:
insert into yotable (pkfield, etc) values ('abcdefgh','whatever')
select * from yotable where seg2 = 'c'
insert into yotable (seg1,seg2,seg3,seg4,seg5,etc)
values ('ab','c','d','ef','gh', 'whatever')
So far all I've found is this CodeProject article which does not go far enough or provide links for further info, and this rudimentary MSDN link.
到目前为止,我发现的所有这些CodeProject文章都不够深入或提供了更多信息的链接,以及这个基本的MSDN链接。
Apparently my google-fu is weak tonight, any links/hints greatly appreciated!
显然我的google-fu今晚很弱,任何链接/提示都非常感谢!
Alternate title: how to simulate 'overlay' fields in SQL SERVER?
替代标题:如何在SQL SERVER中模拟“叠加”字段?
MS SQL SERVER 2005 or later assumed/preferred.
假定/首选MS SQL SERVER 2005或更高版本。
1 个解决方案
#1
1
You can define a CLR UDT that has the structure you want, but (1) you won't be able to keep the foreign keys (foreign keys have to be at the column level, not a field in a UDT in a column), and (2) it isn't trivial to implement a CLR UDT (at least compared to what is in your pseudocode). Plus, from your description, it sounds like they really are separate columns semantically, and what you're looking for is just a shortcut for convenience; IMO a UDT probably isn't the best approach in that scenario anyway.
您可以定义具有所需结构的CLR UDT,但(1)您将无法保留外键(外键必须位于列级别,而不是列中UDT中的字段), (2)实现CLR UDT并不是一件容易的事(至少与你的伪代码相比)。另外,根据您的描述,听起来它们在语义上确实是单独的列,而您正在寻找的只是方便的捷径;无论如何,IMO UDT可能不是那个场景中最好的方法。
I would suggest keeping the separate columns, but create either a view that has a column that concatenates the fields together, or a computed column in the table that does the same. This will permit searching using either the combined or separate notation. You can then use an INSTEAD OF trigger to decompose an insert/update on the combined column into the component parts.
我建议保留单独的列,但创建一个视图,该视图具有将字段连接在一起的列,或者创建相同的表中的计算列。这将允许使用组合或单独的表示法进行搜索。然后,您可以使用INSTEAD OF触发器将组合列上的插入/更新分解为组件部分。
#1
1
You can define a CLR UDT that has the structure you want, but (1) you won't be able to keep the foreign keys (foreign keys have to be at the column level, not a field in a UDT in a column), and (2) it isn't trivial to implement a CLR UDT (at least compared to what is in your pseudocode). Plus, from your description, it sounds like they really are separate columns semantically, and what you're looking for is just a shortcut for convenience; IMO a UDT probably isn't the best approach in that scenario anyway.
您可以定义具有所需结构的CLR UDT,但(1)您将无法保留外键(外键必须位于列级别,而不是列中UDT中的字段), (2)实现CLR UDT并不是一件容易的事(至少与你的伪代码相比)。另外,根据您的描述,听起来它们在语义上确实是单独的列,而您正在寻找的只是方便的捷径;无论如何,IMO UDT可能不是那个场景中最好的方法。
I would suggest keeping the separate columns, but create either a view that has a column that concatenates the fields together, or a computed column in the table that does the same. This will permit searching using either the combined or separate notation. You can then use an INSTEAD OF trigger to decompose an insert/update on the combined column into the component parts.
我建议保留单独的列,但创建一个视图,该视图具有将字段连接在一起的列,或者创建相同的表中的计算列。这将允许使用组合或单独的表示法进行搜索。然后,您可以使用INSTEAD OF触发器将组合列上的插入/更新分解为组件部分。