Click here to Skip to main content
11,928,078 members (38,365 online)
Rate this:
Please Sign up or sign in to vote.
I want to create Foreign key with comma saperator
Cid           int
ColorName     Varchar(50)
PackingId        int
PackingType      varchar(50)
PackingColorIds  varchar(max)

Data like this
Cid        ColorName
1          white
2          Green
3          Orange

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

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...
Posted 5-Nov-12 3:09am
Edited 5-Nov-12 3:26am
Sanjay K. Gupta 5-Nov-12 8:16am
I think, it is not possible. You have to add a child table for holding the foreign-primary relation.
psychic6000 5-Nov-12 8:25am
as far as i know a single record can only be addressed using foreign key (1 to many/ 1 to 1, no many to many)
but for many to many, create a fact table which only holds the primary keys of both tables.
willempipi 5-Nov-12 8:33am
"I want do with this criteria, not want third table Packet-color..."

Ted Codd will be very pleased...
URVISHSUTHAR 3-Jan-13 8:52am
Yup, better to use child table .... :)
Rate this: bad
Please Sign up or sign in to vote.

Solution 1

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
Aarti Meswania 5-Nov-12 8:24am
Thanks but
I know this simple method
but I want do perform on this criteria I have mentioned in Question
Rate this: bad
Please Sign up or sign in to vote.

Solution 2

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...

here are some links, how to do it.

An Easy But Effective Way to Split a String using Transact-SQL[^][^]

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...

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151126.1 | Last Updated 5 Nov 2012
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100