Update change foreign keys - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Update change foreign keys

Author

Topic

abliss
Starting Member

6 Posts

Posted - 2010-10-27 : 20:08:31

I have a DB that is live and that I inherited from an ex employee in mid-stream. Now, when I initially started I was extremely green (actually a complete noob),so I began by reverse engineering the table relationship structures that were already in place.

The problem with the method I chose was the existing tables I had to glean info off of were designed incorrectly, so of course all the tables I added before I realized the error are incorrect too.

Problem: Instead of storing the foreign keys from my look up tables in the target table that is capturing the data, I'm pulling the actual text value instead.

Example: I have a project table that is pulling the location from a location lookup. Lets say my location is Houstion, The project table is actually storing "Houston" instead of the associated unique ID of 10. It is doing the same thing with the Division & Department fields too.

I know that this can be corrected by running a T-Sql script, the only problem is I'm not a T-Sql programmer, I'm still trying to get a handle on running a Sql server while still getting my other duties completed. Could someone please give me an example of what to run to correct the problem with the 3 fields mentioned in the above Example. I could then plug my own tables and attributes in to get the job done.

Thanks

jezemine
Master Smack Fu Yak Hacker

2886 Posts

Posted - 2010-10-28 : 00:20:35

you should post the DDL for the tables that are messed up, otherwise we are guessing at what you need to do.

elsasoft.org

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-10-28 : 01:42:40

What i understand is that in some tables , instead of storing the reference key , actual value is stored.

Example:
In main table
PK Value
10 - USA
20 - AUS

In child table:
1 Test USA (Instead of 10)

What i feel that if you change the value then even code of SP or frontend application may be required to be changed because when you update the value with 10 in the column, the front end will show 10 instead of USA.

Even I would like to know the SQL Experts opinion on how to resolve this kind of issues.

abliss
Starting Member

6 Posts

Posted - 2010-10-28 : 21:51:40

Hello, here's the DDL for my issue. I scaled down the tables and made their relationship very simple, but it still reflects my current issue. Also, here's a link to a pic of "how" the relationship between the tables should of been built. [url]http://imgs.inkfrog.com/pix/davidbliss/ResearchDB_RelationalDiagram1.jpg
[/url]
Thanks

******************



USE ResearchDB

GO
/****** Object:  Table [dbo].[StudyProjects]    Script Date: 10/28/2010 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[StudyProjects](
	[ProjectID] [nvarchar](10) NOT NULL,
	[ProjectTitle] [nvarchar](150) NOT NULL,
	[ProjectSponsor] [nvarchar](75) NOT NULL,
	[Division] [nvarchar](50) NOT NULL,
	[ProjectLocation] [nvarchar](50) NOT NULL,
	[ProjectStatus] [bit] NULL,
 CONSTRAINT [PK_StudyProjects] PRIMARY KEY CLUSTERED 
(
	[ProjectID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[StudyProjects] ([ProjectID], [ProjectTitle], [ProjectSponsor], [Division], [ProjectLocation], [ProjectStatus]) VALUES (N'1012A50', N'Stage 4 Lung Cancer', N'NIH', N'Cancer', N'Marble Falls', NULL)
INSERT [dbo].[StudyProjects] ([ProjectID], [ProjectTitle], [ProjectSponsor], [Division], [ProjectLocation], [ProjectStatus]) VALUES (N'102A51B', N'Childhood Diabetes', N'CDC', N'Pediatrics', N'Bryan College Station', NULL)
/****** Object:  Table [dbo].[ListProjectSponsor]    Script Date: 10/28/2010 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListProjectSponsor](
	[ProjectSponsorID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectSponsor] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ListProjectSponsor] PRIMARY KEY CLUSTERED 
(
	[ProjectSponsorID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ListProjectSponsor] ON
INSERT [dbo].[ListProjectSponsor] ([ProjectSponsorID], [ProjectSponsor]) VALUES (1, N'NIH')
INSERT [dbo].[ListProjectSponsor] ([ProjectSponsorID], [ProjectSponsor]) VALUES (2, N'CDC')
SET IDENTITY_INSERT [dbo].[ListProjectSponsor] OFF
/****** Object:  Table [dbo].[ListProjectLocation]    Script Date: 10/28/2010 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListProjectLocation](
	[ProjectLocationID] [int] IDENTITY(1,1) NOT NULL,
	[ProjectLocation] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ListStudyLocation] PRIMARY KEY CLUSTERED 
(
	[ProjectLocationID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ListProjectLocation] ON
INSERT [dbo].[ListProjectLocation] ([ProjectLocationID], [ProjectLocation]) VALUES (1, N'Bryan College Station')
INSERT [dbo].[ListProjectLocation] ([ProjectLocationID], [ProjectLocation]) VALUES (2, N'Round Rock')
INSERT [dbo].[ListProjectLocation] ([ProjectLocationID], [ProjectLocation]) VALUES (3, N'Waco')
INSERT [dbo].[ListProjectLocation] ([ProjectLocationID], [ProjectLocation]) VALUES (4, N'Marble Falls')
SET IDENTITY_INSERT [dbo].[ListProjectLocation] OFF
/****** Object:  Table [dbo].[ListDivisions]    Script Date: 10/28/2010 19:53:56 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[ListDivisions](
	[DivID] [int] IDENTITY(1,1) NOT NULL,
	[Division] [nvarchar](50) NOT NULL,
 CONSTRAINT [PK_ListDivisions] PRIMARY KEY CLUSTERED 
(
	[DivID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET IDENTITY_INSERT [dbo].[ListDivisions] ON
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (1, N'Cancer')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (2, N'Cardiology')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (3, N'Anesthesia')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (4, N'Pediatrics')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (5, N'Nephrology')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (6, N'Bacteriology')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (7, N'Cardiac Electrophysiology and Pacing')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (8, N'Cardiology')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (9, N'Cardiothoracic Surgery')
INSERT [dbo].[ListDivisions] ([DivID], [Division]) VALUES (10, N'Child/Adolescent Mental Health')
SET IDENTITY_INSERT [dbo].[ListDivisions] OFF

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources