In Oracle, if you insert an empty string ('') to a foreign key column, Oracle inserts NULL that means there is no reference to the parent table, and INSERT statement executes successfully.
SQL Server does not treat empty string and NULL in the same way, and insert statement fails.
Assume there is a parent and child table:
Oracle:
-- Parent table CREATE TABLE states ( abbr CHAR(2) PRIMARY KEY NOT NULL, name VARCHAR2(90) ); -- Insert some rows INSERT INTO states VALUES ('CA', 'California'); INSERT INTO states VALUES ('NY', 'New York'); -- Child table CREATE TABLE cities ( name VARCHAR2(90), state CHAR(2) REFERENCES states(abbr) );
Now if the state is unknown, you can successfully insert an empty string to the state column:
INSERT INTO cities VALUES ('Greenwood' , ''); # 1 row created.
Oracle actually inserts NULL to the state column:
Let's create the same tables in SQL Server:
SQL Server:
-- Parent table CREATE TABLE states ( abbr CHAR(2) PRIMARY KEY NOT NULL, name VARCHAR(90) ); -- Insert some rows INSERT INTO states VALUES ('CA', 'California'); INSERT INTO states VALUES ('NY', 'New York'); -- Child table CREATE TABLE cities ( name VARCHAR(90), state CHAR(2) REFERENCES states(abbr) );
Now if you run the same insert statement in SQL Server, an constraint violation error is raised:
INSERT INTO cities VALUES ('Greenwood' , ''); # Msg 547, Level 16, State 0, Line 1 # The INSERT statement conflicted with the FOREIGN KEY constraint "FK__cities__state__4F47C5E3". # The conflict occurred in database "test", table "dbo.states", column 'abbr'.
So if you application may insert empty strings to a foreign key column, they have to be replaced with NULL when you migrate the application from Oracle to SQL Server:
SQL Server:
INSERT INTO cities VALUES ('Greenwood' , NULL); # 1 row(s) affected
This migration issue often occurs in applications that use string data types and dynamically build INSERT statements by enclosing each string value with single quotes.
If no value is entered to a foreign key column, the application attempts to insert an empty string (''), and the statement fails in SQL Server. You have to modify your application to check for an empty value and insert NULL explicitly.
What if you do not have access to the application source code, and cannot modify the INSERT statement that explicitly inserts an empty string into a foreign key column?
You can create an INSTEAD OF INSERT trigger that replaces the empty string with NULL in SQL Server:
SQL Server:
CREATE TRIGGER cities_tr_iiu ON cities INSTEAD OF INSERT AS BEGIN SET NOCOUNT ON -- Replace empty string with NULL INSERT INTO cities SELECT name, NULLIF(state, '') FROM inserted; END GO
Now you can use empty string in the INSERT statement, and no error is raised:
INSERT INTO cities VALUES ('Greenwood' , ''); # 1 row(s) affected
The trigger replaced '' with NULL:
SQLines offers database administration, optimization and migration services for Oracle and SQL Server databases and applications. For more information, please Contact Us.
Written by Dmitry Tolpeko, dmtolpeko@sqlines.com - August 2012.