Home > Database > Mysql Tutorial > body text

Oracle与MySQL删除字段时对索引与约束的处理对照

WBOY
Release: 2016-06-07 16:07:13
Original
1250 people have browsed it

不知道有多少人清楚的知道,在Oracle中,如果一个复合索引,假定索引(a,b,c)三个字段,删除了(包括unused)其中一个字段,Oracle会怎么处理这个索引。同样,如果是约束,Oracle又怎么处理? 用oracle为例子,我又拿mysql做了一个对比,看看mysql是怎么处理这个

不知道有多少人清楚的知道,在Oracle中,如果一个复合索引,假定索引(a,b,c)三个字段,删除了(包括unused)其中一个字段,Oracle会怎么处理这个索引。同样,如果是约束,Oracle又怎么处理?

用oracle为例子,我又拿mysql做了一个对比,看看mysql是怎么处理这个问题的。我这里不讨论谁好谁差,只是希望大家知道其中的差别与细节而已。

我们先看Oracle的例子,我们创建一个表,然后在上面创建一个约束,创建一个索引:

<p>SQL 10G>create table test(a int,b int,c int); <br>Table created. <br>SQL 10G>alter table test add constraint pk_test primary key (a,b); <br>Table altered. <br>SQL 10G>create index ind_test on test(b,c); <br>Index created. </p>
Copy after login

然后,我们检查刚才创建的约束与索引

<p>SQL 10G>select t.constraint_name,c.constraint_type,t.column_name,t.position,c.status,</p><p>c.validated <br>2 from user_cons_columns t,user_constraints c <br>3 where c.constraint_name=t.constraint_name <br>4 and c.constraint_type != 'C' <br>5 and t.table_name = 'TEST' <br>6 order by constraint_name,position; <br>CONSTRAINT_NAME C COLUMN_NAME POSITION STATUS VALIDATED <br>---------------- - ------------ ---------- -------- ------------- <br>PK_TEST P A 1 ENABLED VALIDATED <br>PK_TEST P B 2 ENABLED VALIDATED <br>SQL 10G>select t.index_name,t.column_name,t.column_position,i.status <br>2 from user_ind_columns t,user_indexes i <br>3 where t.index_name=i.index_name <br>4 and t.table_name = 'TEST' <br>5* order by index_name,column_position <br>INDEX_NAME COLUMN_NAME COLUMN_POSITION STATUS <br>-------------- ------------ --------------- -------- <br>IND_TEST B 1 VALID <br>IND_TEST C 2 VALID </p>
Copy after login


现在,我们先删除索引上的字段,其实并没有物理删除,只是设置为unused:

<p>SQL 10G>ALTER TABLE test SET UNUSED (c); <br>Table altered. <br>SQL 10G>select t.index_name,t.column_name,t.column_position,i.status <br>2 from user_ind_columns t,user_indexes i <br>3 where t.index_name=i.index_name <br>4 and t.table_name = 'TEST' <br>5 order by index_name,column_position; <br>no rows selected </p>
Copy after login

发现了什么,索引也删除了。那我们再删除约束上的字段呢?

<p>SQL 10G>ALTER TABLE test SET UNUSED (b); <br>ALTER TABLE test SET UNUSED (b) <br>* <br>ERROR at line 1: <br>ORA-12991: column is referenced in a multi-column constraint <br>SQL 10G>ALTER TABLE test SET UNUSED (b) CASCADE CONSTRAINTS; <br>Table altered. <br>SQL 10G>select t.constraint_name,c.constraint_type,t.column_name,t.position,c.status,</p><p>c.validated <br>2 from user_cons_columns t,user_constraints c <br>3 where c.constraint_name=t.constraint_name <br>4 and c.constraint_type != 'C' <br>5 and t.table_name = 'TEST' <br>6 order by constraint_name,position; <br>no rows selected </p>
Copy after login

我们可以看到,正常的删除会报一个错误,如果我们指定了cascade,将会把对应的约束也删除。

我们看完了Oracle的处理过程,再看看mysql是这么处理删除索引上字段这个事情的

mysql> create table test(a int,b int,c int); <br>Query OK, 0 rows affected (0.72 sec) <br>mysql> alter table test add primary key(a,b); <br>Query OK, 0 rows affected (0.27 sec) <br>Records: 0 Duplicates: 0 Warnings: 0 <br>mysql> create index ind_test on test(b,c); <br>Query OK, 0 rows affected (0.32 sec) <br>Records: 0 Duplicates: 0 Warnings: 0 <p><br></p>
Copy after login

我们执行同样的操作,先删除复合索引中的一个字段,然后删除约束中的一个字段。

<p>mysql> alter table test drop c; <br>Query OK, 0 rows affected (0.58 sec) <br>Records: 0 Duplicates: 0 Warnings: 0 <br>mysql> show index from test; <br>+-------+------------+----------+--------------+-------------+-----------+ <br>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | <br>+-------+------------+----------+--------------+-------------+-----------+ <br>| test | 0 | PRIMARY | 1 | a | A | <br>| test | 0 | PRIMARY | 2 | b | A | <br>| test | 1 | ind_test | 1 | b | A | <br>+-------+------------+----------+--------------+-------------+-----------+ <br>3 rows in set (0.06 sec) <br>mysql> alter table test drop b; <br>Query OK, 0 rows affected (0.28 sec) <br>Records: 0 Duplicates: 0 Warnings: 0 <br>mysql> show index from test; <br>+-------+------------+----------+--------------+-------------+-----------+ <br>| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | <br>+-------+------------+----------+--------------+-------------+-----------+ <br>| test | 0 | PRIMARY | 1 | a | A | <br>+-------+------------+----------+--------------+-------------+-----------+ <br>1 row in set (0.03 sec) </p>
Copy after login

可以看到,mysql的处理方式是有差别的,mysql仅仅是把字段从索引中拿掉,而不是删除该索引。

本文的意思,就是想提醒大家,平常在做columns删除的时候,包括unused,一定要小心,是否有复合索引包含了该字段,否则,一不小心把索引删除了,可能将引发大的错误。

【相关文章】

  • mysql和Oracle数据库的一些异同
  • Oracle数据库的完整性约束规则详解
  • DB2数据库的约束

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template
About us Disclaimer Sitemap
php.cn:Public welfare online PHP training,Help PHP learners grow quickly!