2024年1月11日发(作者：)

oracle约束学习（1）unique和check

有人说，没有索引, 拿什么来保证约束?姑且不论这话的对错，但约束的实现（除了not null），很多都是通过索引来快速定位约束的地方。unique约束会自动建立索引，pk也是。也因此，约束的很多问题总是和索引缠绵一起。

相关视图：

dba_constraints

dba_cons_columns

not null约束比较特别点。not null的索引类型和check约束一样，都是C。只有not null这种约束才会被继承，记住这一点，在复制表时，别忘了加约束、索引等。

unique 约束

延迟验证属性：deferrable/not deferrable, Deferred/immediate

延迟约束在提交的时候才进行校验，大多数是这样的。

not deferrable：不提交也检查，这是缺省值。

[sql] view plaincopy

1. SQL> select * from hr.t1;

2.

3. ID NAME

4. ---------- ----------------------------------------

5. 1 d

6. 2 b

7.

8. SQL> conn hr/hr

9. 已连接。

10. SQL> alter table t1 add constraint t1_u_1 unique(id); -- 默认情况下是not deferrable & immediate

11.

12. 表已更改。

13.

14. SQL> insert into t1 values(1,'c');

15. insert into t1 values(1,'c')

16. *

17. 第 1 行出现错误:

18. ORA-00001: 违反唯一约束条件 (HR.T1_U_1)

19.

20.

21. SQL> alter table t1 drop constraint t1_u_1 cascade;

22.

23. 表已更改。

24.

25. SQL> alter table t1 add constraint t1_u_2 unique(id) deferrable; --deferrable & immediate

26.

27. 表已更改。

28. SQL> select * from t1;

29.

30. ID NAME

31. ---------- ----------------------------------------

32. 1 d

33. 2 b

34.

35. SQL> insert into t1 values(1,'c');

36. insert into t1 values(1,'c')

37. *

38. 第 1 行出现错误:

39. ORA-00001: 违反唯一约束条件 (HR.T1_U_2)

为什么延迟还还报错呢？延迟性约束，索引必须非唯一。enable novalidate也必须是非唯一索引。然而，我们在建unique约束的时候，oracle会隐士的先创建一个唯一性索引，而且索引的名字和约束的名字是一样的。再来建立约束，除非这个列本来有一个约束。

[sql] view plaincopy

1. SQL> alter table t1 add constraint t1_u_6 unique(id);

2.

3. 表已更改。

4.

5. SQL> select index_name,uniqueness from user_indexes ;

6.

7. INDEX_NAME UNIQUENESS

8. ------------------------------------------------------------ ------------------

9. T1_U_6 UNIQUE

[sql] view plaincopy

1. SQL> alter table t1 add constraint t1_u_3 unique(id) deferrable; --deferrable & immediate

2.

3. 表已更改。

4.

5. SQL> select * from t1;

6.

7. ID NAME

8. ---------- ----------------------------------------

9. 1 d

10. 2 b

11.

12. SQL> insert into t1 values(1,'c');

13. insert into t1 values(1,'c')

14. *

15. 第 1 行出现错误:

16. ORA-00001: 违反唯一约束条件 (HR.T1_U_3)

17.

18.

19. SQL> alter table t1 drop constraint t1_u_3 cascade;

20.

21. 表已更改。

22.

23. SQL> drop index t1_index;

24.

25. 索引已删除。

26.

27. SQL> create unique index t1_index on t1(id);

28.

29. 索引已创建。

30.

31. SQL> select index_name,uniqueness from user_indexes;

32.

33. INDEX_NAME UNIQUENESS

34. ------------------------------------------------------------ ------------------

35. T1_INDEX UNIQUE

36.

37. SQL> alter table t1 add constraint t1_u_4 unique(id) deferrable;

38. alter table t1 add constraint t1_u_4 unique(id) deferrable

39. *

40. 第 1 行出现错误:

41. ORA-01408: 此列列表已索引

42.

43. SQL> drop index t1_index;

44.

45. 索引已删除。

46.

47. SQL> alter table t1 drop constraint t1_u_4 cascade;

48. alter table t1 drop constraint t1_u_4 cascade

49. *

50. 第 1 行出现错误:

51. ORA-02443: 无法删除约束条件 - 不存在的约束条件

52. SQL> alter table t1 add constraint t1_u_5 unique(id) initially deferred deferrable; --若是not deferrable，则只能跟immediate

53.

54. 表已更改。

55.

56. SQL> select * from t1;

57.

58. ID NAME

59. ---------- ----------------------------------------

60. 1 d

61. 2 b

62.

63. SQL> insert into t1 values(1,'c');

64.

65. 已创建 1 行。

66.

67. SQL> insert into t1 values(1,'d');

68.

69. 已创建 1 行。

70.

71. SQL> commit;

72. commit

73. *

74. 第 1 行出现错误:

75. ORA-02091: 事务处理已回退

76. ORA-00001: 违反唯一约束条件 (HR.T1_U_5)

immediate deferrable是立马延迟，而deferred deferrable则是在commit时给回滚掉。

check

validate会检查旧值，而novalidate不会。这个运用可以用来避免前台小姐误操作。

[sql] view plaincopy

1. SQL> select * from t1;

2.

3. ID NAME

4. ---------- ----------------------------------------

5. 1 d

6. 2 b

7. 3 c

8. 4 c

9.

10. SQL> alter table t1 add constraint t1_u_6 check(id>10) validate;

11. alter table t1 add constraint t1_u_6 check(id>10) validate

12. *

13. 第 1 行出现错误:

14. ORA-02293: 无法验证 (HR.T1_U_6) - 违反检查约束条件

15.

16.

17. SQL> alter table t1 add constraint t1_u_6 check(id>10) novalidate;

18.

19. 表已更改。

20.

21. SQL> select * from t1;

22.

23. ID NAME

24. ---------- ----------------------------------------

25. 1 d

26. 2 b

27. 3 c

28. 4 c

29.

30. SQL> insert into t1 values(5,'d');

31. insert into t1 values(5,'d')

32. *

33. 第 1 行出现错误:

34. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

35.

36.

37. SQL> alter table t1 add constraint t1_u_6 check(id>10) enable novalidate;

38.

39. 表已更改。

40. SQL> insert into t1 values(6,'d');

41. insert into t1 values(6,'d')

42. *

43. 第 1 行出现错误:

44. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

45.

46.

47. SQL> select * from t1;

48.

49. ID NAME

50. ---------- ----------------------------------------

51. 1 d

52. 2 b

53. 3 c

54. 4 c

55.

56.

57.

58. SQL> alter table t1 add constraint t1_u_6 check(id>10) disable novalidate;

59.

60. 表已更改。

61.

62. SQL> select * from t1;

63.

64. ID NAME

65. ---------- ----------------------------------------

66. 1 d

67. 2 b

68. 3 c

69. 4 c

70.

71. SQL> insert into t1 values(5,'s');

72.

73. 已创建 1 行。

74.

75. SQL> commit;

76.

77. 提交完成。

约束的优先级比约束的属性来得高。

[sql] view plaincopy

1. SQL> create table t1 (id number,constraint t1_u_6 check(id>3) validate);

2.

3. 表已创建。

4.

5. SQL> insert into t1 values(1);

6. insert into t1 values(1)

7. *

8. 第 1 行出现错误:

9. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

10.

11.

12. SQL> insert into t1 values(5);

13.

14. 已创建 1 行。

15.

16. SQL> drop table t1 purge;

17.

18. 表已删除。

19.

20. SQL> create table t1(id number,constraint t1_u_6 check(id>3) novalidate);

21.

22. 表已创建。

23.

24. SQL> insert into t1 values(1);

25. insert into t1 values(1)

26. *

27. 第 1 行出现错误:

28. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

29.

30.

31. SQL> insert into t1 values(5);

32.

33. 已创建 1 行。

34.

35.

36. SQL> select * from t1;

37.

38. ID

39. ----------

40. 5

41. SQL> alter table t1 add constraint t1_u_6 check(id>10) novalidate;

42.

43. 表已更改。

44.

45. SQL> insert into t1 values(3);

46. insert into t1 values(3)

47. *

48. 第 1 行出现错误:

49. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

50.

51.

52. SQL> insert into t1 values(7);

53. insert into t1 values(7)

54. *

55. 第 1 行出现错误:

56. ORA-02290: 违反检查约束条件 (HR.T1_U_6)

57.

58.

59. SQL> insert into t1 values(11);

60.

61. 已创建 1 行。

62.

63. SQL> select * from t1;

64.

65. ID

66. ----------

67. 5

68. 11