0

How can I insert data that contains an underscore in a foreign key field? E.g. This works:

INSERT INTO [childTable] (1, 'ABC')

But this doesn't work:

INSERT INTO [childTable] (1, '_ABC')

as it gets this error:

"The INSERT statement conflicted with the FOREIGN KEY constraint "FK_childTable_parentTable". The conflict occurred in database "X", table "parentTable", column 'SomeField'".

where we have two tables related by a foreign key:

CREATE TABLE parentTable (
    Id int,
    SomeField [char] (4)
)

CREATE TABLE childTable (
    SomeField [char] (4)
)

ALTER TABLE childTable CONSTRAINT FK_childTable_parentTable
FOREIGN KEY SomeField
REFERENCES parentTable (SomeField)

and where the parentTable definitely contains a record with the '_ABC' data.

If I temporarily remove or disable the foreign key constraint, I can insert the records OK and restore the foreign key OK, but is there a way to escape the underscore or some other better solution?

Environment: SQL Server Management Studio against a Microsoft SQL Server 2008 R2 server and database with the collation order set to Latin1_General_BIN.

philu
  • 795
  • 1
  • 8
  • 17
  • Defining a primary key to the parenttable and the parent having '_ABC' should allow the value to be inserted into the child referring the parent – Recursive Jul 24 '14 at 04:52
  • http://stackoverflow.com/questions/4175431/how-to-enter-special-characters-like-in-oracle-database – smali Jul 24 '14 at 04:53
  • if you remove underscore. Is it working fine.? – smali Jul 24 '14 at 04:54
  • If the underscores are removed from the parent and child records - yes it works fine. – philu Jul 24 '14 at 04:56
  • Is the problem case sensitivity? GENERAL_LATIN_BIN is case sensitive (BIN stands for binary), so the foreign key would need to match character case too. – Gary Walker Jul 24 '14 at 05:11
  • Good question, but that's not the issue; the character cases match. – philu Jul 24 '14 at 05:20
  • 2
    Statements INSERT INTO [childTable] (1, '_ABC') and CREATE TABLE childTable (SomeField [char] (4)) are inconsistent (2 columns vs 1 column). And provided syntax is also wrong. It looks that in reality you have different database schema. So it does not make sense to discuss current one. P.S. Theoretically underscore can have different binary representation (for example for Unicode), so it works wrong with your binary collation. – Maxim Jul 24 '14 at 05:25
  • I generated a script to recreate the two actual tables, changed their names, created the two new tables in the original database, ran the SQL inserts... no error. Retested the data in the original tables... still got the error. – philu Jul 24 '14 at 05:57
  • Maxim is pretty close to the answer: there's something wrong with the data: I created a dummy record in the parent table, complete with underscore, and could link to it in the child table with no problem; the only problem is with the pre-existing data. – philu Jul 24 '14 at 06:15
  • Today, one day later, I ran exactly the same INSERT statement on exaclyt the database/table/data, and it all works. Something is going on that I know nothing about. – philu Jul 25 '14 at 00:24

2 Answers2

0

Simply put - if you're getting the conflicted with foreign key error ABC exists in your parent table and _ABC doesn't. It has nothing to do with the underscore.

Check _ABC exists in your parent table. If it doesn't add it - then your insert into your child table will work fine.

Swomble
  • 874
  • 8
  • 17
  • Checked all the data; it's definitely not a data issue. I suspect it was something to do with the Collation order, but that's not conclusive, and has gone away by itself, so I'm stumped. – philu Jul 25 '14 at 00:27
0

This can happen when you have an INSERT trigger; it's the TRIGGER that gets the error.

philu
  • 795
  • 1
  • 8
  • 17