0

Here is my problem:

I try to add a Foreign key between 2 tables with data already existing in it:

ALTER TABLE  [dbo].[abonnement]     
ADD CONSTRAINT abonnement_fk2 FOREIGN KEY (id_langue)     
REFERENCES [dbo].[langue] (id_langue) ;

the table [abonnement] column id_langue contains 2 different value (EN,FR)
the table [langue] column id_langue contains 2 different value (en,fr)

SQL server (2012) returns me the following error:

The ALTER TABLE statement conflicted with the FOREIGN KEY constraint "abonnement_fk2". The conflict occurred in database "********", table "dbo.langue", column 'id_langue'.

I am not allowed to edit directly the "en, fr" and "EN,FR" data

How do i make my foreign key case case insensitive ?

Thanks for your help.

Gogi
  • 1
  • I closed this since there is a duplicate that shows you what you _want_ to do, but I agree with @Amir's answer that a numeric foreign key is a better long-term choice in general. – D Stanley Apr 03 '17 at 16:52

2 Answers2

1

It is better to make foreign keys by int type columns not string. In this case I suggest you change it to int type and update its value. However if you insist in string keys, change all to lower or upper case.

Alireza Zojaji
  • 802
  • 2
  • 13
  • 33
  • Why is it better to make foreign keys int rather than string? – GarethD Apr 03 '17 at 16:58
  • Simpler reason is to avoid such problems. but really you can make primary keys auto incremented if they are `int` type. and comparing int types is faster and simpler. – Alireza Zojaji Apr 03 '17 at 17:03
  • If a language can be uniquely identified using a 2 character code then isn't a further integer column to uniquely identify it just unnecessary? I actually agree with you, but there are a lot of people who wouldn't, and there are very prominent names in both side of the natural vs surrogate key debate. My point was more that if saying that one thing is better than another, you should be giving reasons as to why. – GarethD Apr 03 '17 at 17:31
  • In addition if the table is fixed and rows are not editable, there is no difference between 2 character string and int, particularly the users need not to see keys in their interfaces. they work with names or abbreviations. but when you have rows changeable or you have high number of rows the integer value keys are absolutely better. – Alireza Zojaji Apr 03 '17 at 17:50
0

You are trying to create a foreign key from [dbo].[abonnement] to [dbo].[langue] but the values in [dbo].[abonnement](id_langue) didn't match with values in [dbo].[langue]. You cannot create a relation which violates referential integrity. Change all to "EN,FR" in both table, "EN,FR"is not equal to "en,fr"

Vecchiasignora
  • 1,275
  • 7
  • 6