Oracle raises ORA-00001 error when an unique constraint is violated by a INSERT or UPDATE statement that attempts to insert a duplicate key. SQL Server raises error 2627 in this case.
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 11g Release 2 Documentation
Microsoft SQL Server 2012 - Books Online
SQLines offers services to migrate Oracle databases and applications to Microsoft SQL Server. For more information, please Contact Us.