ERROR ORA 00001 FOR GHOST COLUMN SYS_NC00008$ IN ORACLE SQL DEVELOPER

Question

I'm getting a unique constraint error in Oracle SQL Developer when I try to insert an entry into the table. While the column it is specifying for the unique constraint error is a ghost column -- SYS_NC00008$

( I've purposely dashed out some values while posting below query here -ignore that)

Query:

INSERT INTO "---"."---" (ID, MESSAGE_TYPE, IS_DELETED, CREATED_BY, CREATED_ON, UPDATED_BY, 
UPDATED_ON) 
VALUES ('2', '----', 'N', 'system', 
TO_TIMESTAMP('2018-08-16 07:51:25.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'), 
'system', TO_TIMESTAMP('2019-09-25 05:07:06.000000000', 'YYYY-MM-DD HH24:MI:SS.FF'))

Error:

ORA-00001: unique constraint (----.----_IX0) violated ORA-06512: at line 1

That's probably a unique index on an expression. Check the list of indexes for that table. — a_horse_with_no_name
Yes I've checked the index name is-- xxx_IX0 and column name is SYS_NC00008$. Still, confused why am I getting the error for it though or how to add entry to it. — Anish Gupta
perhaps there's an insert trigger to populate another table as you populate this one. — Barbaros Özhan

Connor McDonald Connor McDonald · Accepted Answer · 2019-11-10T09:06:27

SYS_NC00008$ and columns like that are due to function based indexes being created.

SQL> create table t ( x int );

Table created.

SQL>
SQL> create index IX on t ( x + 10 );

Index created.

SQL>
SQL> select column_name
  2  from   user_tab_cols
  3  where  table_Name = 'T';

COLUMN_NAME
------------------------------
X
SYS_NC00002$

Do a query on USER_IND_EXPRESSIONS to see the contents of the index, and that will tell you where the unique violation is coming from.

SQL> select COLUMN_EXPRESSION
  2  from    user_ind_expressions
  3  where  index_name = 'IX';

COLUMN_EXPRESSION
--------------------------------------
"X"+10

ERROR ORA 00001 FOR GHOST COLUMN SYS_NC00008$ IN ORACLE SQL DEVELOPER

1 Answers