February 6, 2006 at 11:17 am
I have 2 tables that I am trying to relate with a foreign key
Tbl_Currencies
ISOCurrencyCode char(3) not null - this is the PK
Tbl_Accounts
BaseCurrency char(3) null
When I run the following statement -
ALTER TABLE
[dbo].[Tbl_Accounts]
ADD CONSTRAINT
[FK_Tbl_Accounts_BaseCurrency]
FOREIGN KEY
([BaseCurrency])
REFERENCES
[dbo].[Tbl_Currencies] ([ISOCurrencyCode])
GO
I receive this error -
ALTER TABLE statement conflicted with COLUMN FOREIGN KEY constraint 'FK_Tbl_Accounts_BaseCurrency'.
The conflict occurred in database 'BGI', table 'Tbl_Currencies', column 'ISOCurrencyCode'.
There are no other SQL objects wit the same name - and the key did exist - I dropped it to change the PK on Tbl_Currencies from non-clustered to cluestered - but it won't let me re-create it after the PK was rebuilt. I also checked the records in Tbl_Accounts - there are a few records with a null value in Basecurrency but all other records have a matching record in Tbl_Currencies.
Thanks for any help,
Harley
February 6, 2006 at 11:59 am
"I also checked the records in Tbl_Accounts - there are a few records with a null value in Basecurrency but all other records have a matching record in Tbl_Currencies."
Why do you have null values..if you are using that field as a relational constraint?
Mathew J Kulangara
sqladventures.blogspot.com
February 6, 2006 at 1:13 pm
Mathew,
Most of the accounts deal with a particular currency type - but there are a few that do not - for those that do, we want to be sure that the currency code selected is valid. With a FK in place, SQL allows either a null value or enforces the match.
I did find the problem - there was record in each table with an empty string - once the value was reset to NULL, the FK was created without further problems.
Thanks for your help,
Harley
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply