Oracle 具有非唯一索引的主键

SQL> CREATE TABLE TAB_TEST ( ID NUMBER, NAME VARCHAR2(10) );

Table created.

SQL> ALTER TABLE TAB_TEST ADD CONSTRAINT ID_PK PRIMARY KEY (ID) USING INDEX ( CREATE INDEX ID_0P ON TAB_TEST(ID ASC) );

Table altered.

SQL> COL INDEX_NAME FOR A10
SQL> COL INDEX_TYPE FOR A10
SQL> COL TABLE_NAME FOR A10
SQL> COL UNIQUENESS FOR A15
SQL> COL STATUS FOR A10
SQL> SELECT INDEX_NAME, INDEX_TYPE, TABLE_NAME, UNIQUENESS, STATUS FROM DBA_INDEXES WHERE TABLE_NAME = 'TAB_TEST';

INDEX_NAME INDEX_TYPE TABLE_NAME UNIQUENESS      STATUS
---------- ---------- ---------- --------------- ----------
ID_0P      NORMAL     TAB_TEST   NONUNIQUE       VALID

SQL> INSERT INTO TAB_TEST VALUES ( 1, 'J1' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 2, 'J2' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 3, 'J3' );

1 row created.

SQL> INSERT INTO TAB_TEST VALUES ( 1, 'J1' );
INSERT INTO TAB_TEST VALUES ( 1, 'J1' )
*
ERROR at line 1:
ORA-00001: unique constraint (FC_DBA1.ID_PK) violated
复制