Solved

relationships and improving primary key in parent table

Posted on 2008-10-13
2
189 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
In this video, viewers are given an introduction to using the Windows 10 Snipping Tool, how to quickly locate it when it's needed and also how make it always available with a single click of a mouse button, by pinning it to the Desktop Task Bar. Int…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

632 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