--第一步:创建表,及相应的主键约束pk_test_id
SQL> create table test(id number constraint pk_test_id primary key);Table created.
--第二步:此时总共有9个约束
SQL> select constraint_name from user_constraints;CONSTRAINT_NAME
------------------------------
BIN$vL7ij8sVLTjgQAB/AQAPeg==$0
PK_TEST_ID
BIN$vL7ij8sYLTjgQAB/AQAPeg==$0
BIN$uvy0dxvyoYvgQAB/AQAMpQ==$0
BIN$uvy0dxwAoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvsoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvvoYvgQAB/AQAMpQ==$0
BIN$uvy0dxv6oYvgQAB/AQAMpQ==$0
BIN$uvy0dxv9oYvgQAB/AQAMpQ==$09 rows selected.
--第三步:删除表,按理说相应的约束应该被干掉了
SQL> drop table test;Table dropped.
--第四步:此时还有9个约束,只是把约束的名称改了下,难道相应的约束没被干掉?
SQL> select constraint_name from user_constraints;CONSTRAINT_NAME
------------------------------
BIN$vL7ij8sVLTjgQAB/AQAPeg==$0
BIN$vL7ij8sYLTjgQAB/AQAPeg==$0
BIN$vL7ij8sbLTjgQAB/AQAPeg==$0
BIN$uvy0dxvyoYvgQAB/AQAMpQ==$0
BIN$uvy0dxwAoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvsoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvvoYvgQAB/AQAMpQ==$0
BIN$uvy0dxv6oYvgQAB/AQAMpQ==$0
BIN$uvy0dxv9oYvgQAB/AQAMpQ==$09 rows selected.请高手们帮忙解释下,谢谢。
SQL> create table test(id number constraint pk_test_id primary key);Table created.
--第二步:此时总共有9个约束
SQL> select constraint_name from user_constraints;CONSTRAINT_NAME
------------------------------
BIN$vL7ij8sVLTjgQAB/AQAPeg==$0
PK_TEST_ID
BIN$vL7ij8sYLTjgQAB/AQAPeg==$0
BIN$uvy0dxvyoYvgQAB/AQAMpQ==$0
BIN$uvy0dxwAoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvsoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvvoYvgQAB/AQAMpQ==$0
BIN$uvy0dxv6oYvgQAB/AQAMpQ==$0
BIN$uvy0dxv9oYvgQAB/AQAMpQ==$09 rows selected.
--第三步:删除表,按理说相应的约束应该被干掉了
SQL> drop table test;Table dropped.
--第四步:此时还有9个约束,只是把约束的名称改了下,难道相应的约束没被干掉?
SQL> select constraint_name from user_constraints;CONSTRAINT_NAME
------------------------------
BIN$vL7ij8sVLTjgQAB/AQAPeg==$0
BIN$vL7ij8sYLTjgQAB/AQAPeg==$0
BIN$vL7ij8sbLTjgQAB/AQAPeg==$0
BIN$uvy0dxvyoYvgQAB/AQAMpQ==$0
BIN$uvy0dxwAoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvsoYvgQAB/AQAMpQ==$0
BIN$uvy0dxvvoYvgQAB/AQAMpQ==$0
BIN$uvy0dxv6oYvgQAB/AQAMpQ==$0
BIN$uvy0dxv9oYvgQAB/AQAMpQ==$09 rows selected.请高手们帮忙解释下,谢谢。
为什么不加WHERE table_name = 'TEST'的条件呢?
如果不加条件,起不是把其他表的约束内容都选出来了?
Table created
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
PK_TEST_ID
SQL> drop table test purge;
Table dropped
SQL> select constraint_name from user_constraints;
CONSTRAINT_NAME
------------------------------
SQL>
确实如此,多谢了,不过有点不明白,“表”对象被删除后,user_tables就彻底把它干掉了,只能到recyclebin垃圾回收站里才能找到。
为什么“约束”对象被删除后,不但在user_constraints 里存了一份“垃圾”数据,而且在recyclcebin里也存了一份“垃圾” 数据,
表删除了,user_table里还是可以找到的,只是表名改了
没有啊,我这边好像看不到,难道要设置什么东西?
SSQL> select table_name from user_tables;no rows selectedSQL> select constraint_name from user_constraints;no rows selectedSQL> create table test(id number constraint pk_test_id primary key);Table created.SQL> drop table test;Table dropped.SQL> select table_name from user_tables;no rows selectedSQL> select constraint_name from user_constraints;CONSTRAINT_NAME
------------------------------
BIN$vL+JSdTDgIbgQAB/AQAP5Q==$0SQL>
SQL> create table test(id number constraint pk_test_id primary key);
Table created
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
TEST
SQL> select table_name,constraint_name from user_constraints;
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
TEST PK_TEST_ID
SQL> drop table test;
Table dropped
SQL> select table_name from user_tables; --BIN$VnncqINTRy+7ruGTE76IlQ==$0为删除后的表名
TABLE_NAME
------------------------------
BIN$VnncqINTRy+7ruGTE76IlQ==$0
SQL> select table_name,constraint_name from user_constraints; --table_name跟user_table的表名相同,可以关联上
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------
BIN$VnncqINTRy+7ruGTE76IlQ==$0 BIN$nIZCU2gMRrujmIOafsDtBg==$0
SQL> flashback recyclebin; --清空回收站
Done
SQL> select table_name from user_tables;
TABLE_NAME
------------------------------
SQL> select table_name,constraint_name from user_constraints;
TABLE_NAME CONSTRAINT_NAME
------------------------------ ------------------------------