Solved

relationships and improving primary key in parent table

Posted on 2008-10-13
2
184 Views
Last Modified: 2012-05-05
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.

0
Comment
Question by:LukeB
2 Comments
 
LVL 4

Expert Comment

by:randy_knight
ID: 22707758
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
 
LVL 39

Accepted Solution

by:
BrandonGalderisi earned 250 total points
ID: 22707856
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…
Delivering innovative fully-managed cloud services for mission-critical applications requires expertise in multiple areas plus vision and commitment. Meet a few of the people behind the quality services of Concerto.

919 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now