Foreign Key / Relation

R

rmatthew

Well-known member
Joined
Dec 30, 2002
Messages
115
Location
Texas
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...
 
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.
 
PlausiblyDamp said:
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.
Click to expand...
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
 
Back
Top