-1

I'm having an issue trying to re-enable a unique constraint. I try using this command:

alter table TESTSCHEMA_1.TEST_TABLE1 enable constraint TEST_UNIQUE_CONSTRAINT1;

The issue is that i have multiple schemas (say: TESTSCHEMA_1 to _5), and they all have tables with the same name TEST_TABLE1 which also have a Unique constraint with the same name TEST_UNIQUE_CONSTRAINT1.

As a result I get this error:

ORA-02299: cannot validate (TESTSCHEMA_1.TEST_UNIQUE_CONSTRAINT1) - duplicate keys found

How can I indicate specifically the schema where is the constraint i want to enable? I've tried using TESTSCHEMA_1.TEST_UNIQUE_CONSTRAINT1, but it throws a syntax error (Non-properly ended sql command)

Roberto Arias
  • 133
  • 1
  • 8
  • 1
    Do you want to remove the existing duplicates? – Mihai Mar 12 '15 at 20:08
  • 1
    Did you check for duplicate keys? The error message seems to be telling you to resolve duplicate keys before the dbms can apply a unique constraint. – Mike Sherrill 'Cat Recall' Mar 12 '15 at 20:11
  • 1
    As for your last question, you are already specifying the schema by putting `TESTSCHEMA_1` in front of the table name. The constraint will reside in the same schema as its parent table so you don't need to (and can't) specify it again. – Chris Hep Mar 12 '15 at 20:13

1 Answers1

0

check uniqueness of your data.

select unique_column_in_test_table1, count(unique_column_in_test_table1) from TESTSCHEMA_1.TEST_TABLE1
group by unique_column_in_test_table1
having count(unique_column_in_test_table1) > 1

if any rows return by this query you have to handle/correct it to be unique.

Eng. Samer T
  • 6,465
  • 6
  • 36
  • 43
  • I see, when i got the error i thought the issue was because of the multiple schemas having unique constraints with the same name, but it is in the data within the constraint. Thank you :) – Roberto Arias Mar 12 '15 at 21:28