Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL-server-2005
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
 

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
v2
Comments
Sanjay K. Gupta at 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 at 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 at 5-Nov-12 8:33am
   
"I want do with this criteria, not want third table Packet-color..."
 
Ted Codd will be very pleased...
URVISHSUTHAR at 3-Jan-13 8:52am
   
Yup, better to use child table .... :)
Rate this: bad
good
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
  Permalink  
v2
Comments
Aarti Meswania at 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
good
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[^]
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...
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 382
1 DamithSL 275
2 OriginalGriff 245
3 Zoltán Zörgő 149
4 Dave Kreskowiak 110
0 OriginalGriff 7,575
1 DamithSL 5,529
2 Sergey Alexandrovich Kryukov 5,279
3 Maciej Los 4,961
4 Kornfeld Eliyahu Peter 4,539


Advertise | Privacy | Mobile
Web01 | 2.8.141223.1 | Last Updated 5 Nov 2012
Copyright © CodeProject, 1999-2014
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