用逗号分隔符存储数据时应用ForeignKey [英] Apply ForeignKey when data stored with comma saprator

问题描述

我想用逗号分隔符创建外键
例如

I want to create Foreign key with comma saperator
e.g

Colors
-----------
Cid           int
ColorName     Varchar(50)

Packets
------------
PackingId        int
PackingType      varchar(50)
PackingColorIds  varchar(max)

像这样的数据

Data like this

Colors
-----------
Cid        ColorName
1          white
2          Green
3          Orange


Packets
------------------
PackingId        PackingType      PackingColorIds
1                Midium           1,2
2                Large            1,2,3

现在，我希望对该数据包表 PackingColorIds Column
限制外键
如果有可能，那么如何在sql中实现呢?

我要使用此标准，而不要第三张表数据包颜色...

Now I want constraint Of Foreign key over this Packets table PackingColorIds Column

If it is possible then how to achieve this in sql?

I want do with this criteria, not want third table Packet-color...

推荐答案

Create a link-table(PacketColors) for the colorid's with 2 columns:

1. PagingId
2. ColorId

Then lose you comma sepated field.

This way you'll get the following reference:

PackingId <=== PacketColors ===> Colors

i dont know if this scenario is exactly what you need, or you are just giving an example of what you want,
for this case you might need something like a string split function, once you have your values you will be comparing those with color id. you can write procedures etc to get that. but this only make things bigger and complex... may be your actual solution worth the complexity or not...

这是一些链接，如何操作.

使用字符串分割字符串的简单而有效的方法Transact-SQL [ http://social.msdn.microsoft.com/论坛/en-US/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85 [

here are some links, how to do it.

An Easy But Effective Way to Split a String using Transact-SQL[^]
http://social.msdn.microsoft.com/forums/en-US/transactsql/thread/4126a010-6885-4eb0-b79c-c798c90edb85[^]

once you have your string as record, you can use them like

select * from color_table where color id in (select * from split_result)

split result = complete logic of your splitting method...

这篇关于用逗号分隔符存储数据时应用ForeignKey的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！