ORA-00001 - Unique Constraint Violated - Oracle to SQL Server Migration

Oracle raises ORA-00001 error when an unique constraint is violated by a INSERT or UPDATE statement that attempts to insert a duplicate key.

Last Update: Oracle 11g R2 and Microsoft SQL Server 2012

Assume there is a table with the primary key in Oracle:

Oracle:

   CREATE TABLE states
   (
      id CHAR(2) PRIMARY KEY,
      name VARCHAR2(90)
   );
 
   -- Let's insert a row
   INSERT INTO states VALUES ('MO', 'Missouri');
   # 1 row created.

Now let's try to insert a row that violates the unique constraint enforced by the primary key:

Oracle:

   -- Assign MO instead of MT abbreviation to Montana by mistake 
   INSERT INTO states VALUES ('MO', 'Montana');
   # ERROR at line 1:
   # ORA-00001: unique constraint (ORA.SYS_C0014290) violated

You can see that ORA-00001 error is raised when an unique constraint is violated in Oracle.

Now let's create the same table with the primary key in SQL Server:

SQL Server:

   CREATE TABLE states
   (
      id CHAR(2) PRIMARY KEY,
      name VARCHAR(90)
   );
 
   -- Let's insert a row
   INSERT INTO states VALUES ('MO', 'Missouri');
   # 1 row created.

Now we will try to insert a row that violates the unique constraint enforced by the primary key:

SQL Server:

   -- Assign MO instead of MT abbreviation to Montana by mistake 
   INSERT INTO states VALUES ('MO', 'Montana');
   # Msg 2627, Level 14, State 1, Line 1
   # Violation of PRIMARY KEY constraint 'PK__states__3213E83FFFE97CF5'. 
   # Cannot insert duplicate key in object 'dbo.states'.
   # The duplicate key value is (MO).

SQL Server raises error 2627 with the severity level 14 when a primary key is violated.

You can also call @@ERROR function to get the error code in SQL Server:

SQL Server:

  -- Get the error code of the last Transact-SQL statement
  SELECT @@ERROR
  # 2627

• Oracle to SQL Server Migration Knowledge Base
• SQL Server for Oracle DBAs and Developers

Oracle 11g Release 2 Documentation

• ORA-00000 to ORA-00852 Error Messages

Microsoft SQL Server 2012 - Books Online

• @@ERROR Function

SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.