轉載於:--&gt>

Oracle主鍵、唯一鍵與唯一索引的區別

一般，我們看到術語“索引”和“鍵”交換使用，但實際上這兩個是不同的。索引是儲存在中的一個物理結構，鍵純粹是一個邏輯概念。鍵代表建立來實施業務規則的完整性約束。索引和鍵的混淆通常是由於資料庫使用索引來實施完整性約束。

接下來我們看看資料庫中的主鍵約束、唯一鍵約束和唯一索引的區別。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

SQL>select*fromv$version;

BANNER

--------------------------------------------------------------------------------

OracleDatabase11g Enterprise Edition Release 11.2.0.1.0 - Production

PL/SQL Release 11.2.0.1.0 - Production

CORE 11.2.0.1.0 Production

TNSforLinux: Version 11.2.0.1.0 - Production

NLSRTL Version 11.2.0.1.0 - Production

SQL>createtabletest (

2 idint,

3 namevarchar2(20),

4 constraintpk_testprimarykey(id))

5 tablespace users;

Tablecreated.

SQL>selectconstraint_name, constraint_typefromuser_constraints;

CONSTRAINT_NAME C

------------------------------ -

PK_TEST P

在test表中，我們指定了ID列作為主鍵，Oracle資料庫會自動建立一個同名的唯一索引：

1

2

3

4

5

6

7

SQL>selectindex_name, index_type, uniqueness, tablespace_name

2 fromuser_indexes

3 wheretable_owner='SCOTT'

4 andtable_name ='TEST';

INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME

-------------------- -------------------- --------- ------------------------------

PK_TEST NORMAL UNIQUE USERS

此時，如果我們再試圖在ID列上建立一個唯一索引，Oracle會報錯，因為該列上已經存在一個唯一索引：

1

2

3

4

5

SQL>createuniqueindexidx_test_ukontest(id);

createuniqueindexidx_test_ukontest(id)

*

ERRORatline 1:

ORA-01408: suchcolumnlist already indexed

即使建立非唯一索引也不行：

1

2

3

4

5

SQL>createindexidx_test_idontest(id);

createindexidx_test_idontest(id)

*

ERRORatline 1:

ORA-01408: suchcolumnlist already indexed

那麼唯一鍵約束的情況是怎樣的呢？

1

2

3

4

5

6

7

8

9

10

11

12

13

SQL>droptabletest purge;

Tabledropped.

SQL>createtabletest(

2 idint,

3 namevarchar2(20),

4 constraintuk_testunique(id));

Tablecreated.

SQL>selectconstraint_name, constraint_typefromuser_constraints;

CONSTRAINT_NAME C

------------------------------ -

UK_TEST U

檢視此時的索引情況：

1

2

3

4

5

6

7

SQL>selectindex_name, index_type, uniqueness, tablespace_name

2 fromuser_indexes

3 wheretable_owner='SCOTT'

4 andtable_name ='TEST';

INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME

-------------------- -------------------- --------- ------------------------------

UK_TEST NORMAL UNIQUE USERS

Oracle同樣自動建立了一個同名的唯一索引，而且也不允許再在此列上建立唯一索引或非唯一索引。

我們知道，主鍵約束要求列值非空（NOT NULL），那麼唯一鍵約束是否也要求非空呢？

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

SQL>insertintotestvalues(1,'Sally');

1 row created.

SQL>insertintotestvalues(null,'Tony');

1 row created.

SQL>insertintotestvalues(null,'Jack');

1 row created.

SQL>select*fromtest;

IDNAME

---------- --------------------

1 Sally

Tony

Jack

從實驗結果來看，唯一鍵約束並沒有非空要求。

接下來我們看看唯一索引對列值的非空要求有什麼不同。

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

SQL>droptabletest purge;

Tabledropped.

SQL>createtabletest(

2 idint,

3 namevarchar2(20));

Tablecreated.

SQL>createuniqueindexidx_test_idontest (id);

Indexcreated.

SQL>insertintotestvalues(1,'Sally');

1 row created.

SQL>insertintotestvalues(null,'Tony');

1 row created.

SQL>insertintotestvalues(null,'Jack');

1 row created.

SQL>select*fromtest;

IDNAME

---------- --------------------

1 Sally

Tony

Jack

透過實驗，我們看出唯一索引與唯一鍵約束一樣對列值非空不做要求。

如果我們讓主鍵約束或者唯一鍵約束失效，Oracle自動建立的唯一索引是否會受到影響？

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

SQL>droptabletest purge;

Tabledropped.

SQL>createtabletest(

2 idint,

3 namevarchar2(20),

4 constraintuk_testunique(id));

Tablecreated.

SQL>selectindex_name, index_type, uniquenessfromuser_indexes;

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

UK_TEST NORMAL UNIQUE

SQL>altertabletest disableconstraintuk_test;

Tablealtered.

SQL>selectindex_name, index_type, uniquenessfromuser_indexes;

norowsselected

SQL>altertabletest disableconstraintuk_test;

Tablealtered.

SQL>selectindex_name, index_type, uniquenessfromuser_indexes;

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

UK_TEST NORMAL UNIQUE

當主鍵約束或者唯一鍵約束失效時，Oracle會標記隱式建立的唯一索引為刪除狀態。

=====================================================

Using Nonunique Indexes to Enforce Uniqueness

You can use an existing nonunique index on a table to enforce uniqueness, either for UNIQUE constraints or the unique ect of a PRIMARY KEY constraint. The advantage of this approach is that the index remains available and valid when the constraint is disabled. Therefore, enabling a disabled UNIQUE or PRIMARY KEY constraint does not require rebuilding the unique index associated with the constraint. This can yield significant time savings on enable operations for large tables.

Using a nonunique index to enforce uniqueness also lets you eliminate redundant indexes. You do not need a unique index on a primary key column if that column is included as the prefix of a composite index. You can use the existing index to enable and enforce the constraint. You also save significant space by not duplicating the index. However, if the existing index is partitioned, then the partitioning key of the index must also be a subset of the UNIQUE key; otherwise, Database creates an additional unique index to enforce the constraint.

=====================================================

如果我們先建立唯一索引，再建立主鍵或者唯一鍵約束，情況又會怎樣呢？

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

39

40

41

42

43

44

45

46

47

48

49

50

51

52

53

54

55

56

57

58

59

60

61

62

63

64

65

66

67

68

69

70

71

72

73

74

75

76

77

78

79

80

81

82

83

84

85

86

87

88

89

90

91

92

93

94

95

96

97

98

SQL>droptabletest purge;

Tabledropped.

SQL>createtabletest(

2 idint,

3 namevarchar2(20));

Tablecreated.

SQL>createuniqueindexidx_test_idontest (id);

Indexcreated.

SQL>selectindex_name, index_type, uniqueness

2 fromuser_indexes

3 wheretable_owner ='SCOTT'

4 andtable_name ='TEST';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

IDX_TEST_ID NORMAL UNIQUE

SQL>altertabletestaddconstraintuk_testunique(id);

Tablealtered.

SQL>selectindex_name, index_type, uniqueness

2 fromuser_indexes

3 wheretable_owner ='SCOTT'

4 andtable_name ='TEST';

INDEX_NAME INDEX_TYPE UNIQUENES

------------------------------ --------------------------- ---------

IDX_TEST_ID NORMAL UNIQUE

SQL>selectconstraint_name, constraint_type

2 fromuser_constraints

3 wheretable_name ='TEST';

CONSTRAINT_NAME C

------------------------------ -

UK_TEST U

SQL>altertabletest disableconstraintuk_test;

Tablealtered.

SQL>selectconstraint_name, constraint_type, status

2 fromuser_constraints

3 wheretable_name ='TEST';

CONSTRAINT_NAME C STATUS

------------------------------ - --------

UK_TEST U DISABLED

SQL>selectindex_name, index_type, uniqueness, status

2 fromuser_indexes

3 wheretable_owner ='SCOTT'

4 andtable_name ='TEST';

INDEX_NAME INDEX_TYPE UNIQUENES STATUS

------------------------------ --------------------------- --------- --------

IDX_TEST_ID NORMAL UNIQUE VALID

實驗結果表明，先建立的唯一索引不受約束失效的影響。

SCOTT@ orcl>createindexidx_test_idontest(id);

Indexcreated.

SCOTT@ orcl>altertabletestaddconstraintuk_testunique(id);

Tablealtered.

SCOTT@ orcl>insertintotestvalues(1,'liu');

insertintotestvalues(1,'liu')

*

ERRORatline 1:

ORA-00001:uniqueconstraint(SCOTT.UK_TEST) violated

SCOTT@ orcl>altertabletestdropconstraintuk_test;

Tablealtered.

SCOTT@ orcl>insertintotestvalues(1,'liu');

1 row created.

SCOTT@ orcl>deletefromtestwhereid=1andrownum=1;

1 row deleted.

SCOTT@ orcl>select*fromtest;

IDNAME

---------- --------------------

yang

1 liu

SCOTT@ orcl>createuniqueindexidx_test_idontest(id);

Indexcreated.

SCOTT@ orcl>altertabletestaddconstraintuk_testunique(id);

Tablealtered.

SCOTT@ orcl>altertabletestdropconstraintuk_test;

Tablealtered.

SCOTT@ orcl>insertintotestvalues(1,'liu');

insertintotestvalues(1,'liu')

*

ERRORatline 1:

ORA-00001:uniqueconstraint(SCOTT.IDX_TEST_ID) violated

總結如下：

（1）主鍵約束和唯一鍵約束均會隱式建立同名的唯一索引，當主鍵約束或者唯一鍵約束失效時，隱式建立的唯一索引會被刪除；

（2）主鍵約束要求列值非空，而唯一鍵約束和唯一索引不要求列值非空；

（3）相同欄位序列不允許重複建立索引；

Oracle主鍵、唯一鍵與唯一索引的區別

相關文章