Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

relationships and improving primary key in parent table

Posted on 2008-10-13
2
Medium Priority
?
192 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 1000 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

Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

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…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Sometimes it takes a new vantage point, apart from our everyday security practices, to truly see our Active Directory (AD) vulnerabilities. We get used to implementing the same techniques and checking the same areas for a breach. This pattern can re…

664 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