Foreign Key / Relation

[rmatthew]

Is it possible to create a Foreign key on part of a field in MSSQL?

 

i have a field that is a delimited list of values contained in another table that i would like to establish a relation...

[PlausiblyDamp]

Foreign keys are defined at the field level - if a field contains a list of values then it would probably be better to break the field down into either multiple fields or a related table.

[Joe Mamma]

Foreign keys are defined at the field level - if a field contains a list of values then it would probably be better to break the field down into either multiple fields or a related table.

as plaus said, you need to do this.

 

A delimited set of values in one field fails to satisfy first normal form -

 

in this thread is a SQL split function

 

after executing the script to create the function you can do the following -

 

given a table like this

 

OriginalTable

Key (int), Description (varchar(255)), DELIMITEDFIELD (varchar(8000))

 

execute this sql script -

 

' you can execute this entire script in one DBCommand.ExecuteNonQuery call

declare @key int

declare @DELIMITEDFIELD int

create table DELIMITEDFIELDTABLE(

originalKey (ID) references OriginalTable(Key),

order int,

value varchar(255), primary key (originalKey, order)

 

declare curs for select key, DELIMITEDFIELD from OriginalTable

open curs

fetch next from curs into @key, @DELIMITEDFIELD

while @@fetch_status = 0

begin

insert into DELIMITEDFIELDTABLE (originalKey, order, value)

select @KEY, ID, VALUE from dbo.split(@DELIMITEDFIELD, ',')

fetch next from curs into @key, @DELIMITEDFIELD

end

close curs

deallocate curs

alter table OriginalTable drop column DELIMITEDFIELD

[Joe Mamma]

my example assumes comma delimited