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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
BrandonGalderisiCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.