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
Solved

Is it possible to create a Foreign Key from BIGINT to NUMERIC(19,0) in SQL Server 2005?

Posted on 2008-06-19
4
369 Views
Last Modified: 2012-06-27
I have two tables.

TEST1
TEST_KEY numeric(19,0) IDENTITY,
TEST_DESC varchar(20)

TEST2
TEST_KEY BIGINT NOT NULL
TEST_NAME varchar(50)

I am trying to use SQL Analysis Server to create a relationship between those two tables but I get an error that the data types are different. Is there a setting to let SQL Server know that they are similar and to allow the relationship to be built?
0
Comment
Question by:gumby34
  • 2
4 Comments
 
LVL 2

Accepted Solution

by:
vasureddym earned 125 total points
ID: 21823055
There are 2 rules to create a foreign key:
1) There should be a primary key on the master table. In your example, it is not clear about the primary key.
2) The datatypes of the Primary key in the master table and the referring column in the child table must be same. in your example, the two columns are of different datatype  numeric(19,0) and Bigint.

If these two rules are not met SQL Server doesn't allow to create FK.
0
 
LVL 60

Expert Comment

by:chapmandew
ID: 21823053
I am pretty sure that AA requires that the two fields be of the exact same data type...
0
 

Author Comment

by:gumby34
ID: 21823180
vasureddym: Thanks I forgot to include the PK info. Both tables have the PK defined as the TEST_KEY.

chapmandew: Thanks for the reply

I guess that we will have to change the datatypes on the second table to numeric(19,0) in order to get this to work.
0
 

Author Closing Comment

by:gumby34
ID: 31468822
Thanks for your help
0

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

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 we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used.

791 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