How to INSERT a field that contains an underscore and is also a foreign key

How can I insert data that contains an underscore in a foreign key field? Eg 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.

---

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.

链接地址: http://www.djcxy.com/p/76310.html

上一篇: 唯一索引上的外键不适用于NULL列

下一篇: 如何插入包含下划线并且也是外键的字段