relationships and improving primary key in parent table

Hello

I am moving a very large MDB to SQLServer v2005. The MDB has two tables, a primary table with s TEXT field as the primary key field and a second table with a TEXT field and a SINGLE field as the key:

PrimaryTable:
  HoleID (text)

SecondayTable:
  HoleID (text)
  Depth (single)

there is full referential integrity between these 2 tables (cascade update, cascade delete)

I read in a SQL book that when defining relationships I should determine if the primnary key in the parent table can be improved upon by using a Integer data types to increase the speed of the relationship. So add an indentiy field to the parent table and use that as the primary key instead of the text field.

it goes on to say that "with the new key, queries that join the table to other tables based on this key will preform much faster and db will be smaller"

what I don't understand is should I add an indentiy field to the secondary table? otherwise how does the indenty field in the child table get to be the same as the parent table's indentity field.

LVL 1
LukeBAsked:
Who is Participating?
 
BrandonGalderisiConnect With a Mentor Commented:
Let's say your tables are called Hole and HoleDepth

--one line of sql
alter table Hole add Hole_ID int identity not null

Now I realize this could be confusing since you already have HOLEID, but since the column you are adding is the ID, it should be named as such.  But regardless of what you choose, add the same name below.

--begin sql
alter table HoleDepth
add Hole_ID int null



update hd
set hole_id = h.hole_id -- note, we are using the second column here
from holedepth hd
join hold h
on h.holeid = hd.holeid -- and the original column here

create index idx_holedepth_hole_id on holedepth (hole_id)

if exists (select null from holedepth where hole_id is null) -- there must be a record in holedepth that is not he hole
begin
-- add the data
insert into hole (holeID)
select holeID from holedepth
where hole_id is null

--repeat the update
update hd
set hole_id = h.hole_id -- note, we are using the second column here
from holedepth hd
join hold h
on h.holeid = hd.holeid -- and the original column here
where hd.hole_id is null --limiting only to the newly inserted items


end

-- now make the column non-nullable and FK it
alter table holedepth alter column hole_id int not null
alter table holedepth add constraint fk_holedepth_hole foreign key (hole_id) references hole (hole_ide)

--break any existing relationship here and drop the old holeID column since the holeID can be retrieved from the parent table by joining on hole_id
--end sql

0
 
randy_knightCommented:
You will need to add a non-identity HoleID field in the 2nd table, then upate it's values with the assigned HoleID in the first table.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.