This is an old revision of the document!


Inserting Empty String to Foreign Key - Oracle to SQL Server Migration

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.

Oracle Inserts NULL When Empty String is Inserted to a Foreign Key Column

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:

SQL Server Raises an Error When Empty String is Inserted to a Foreign Key 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

Applications That Use String Literals

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.

If You Cannot Modify the Application

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:

Resources

SQLines Services

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.

You could leave a comment if you were logged in.