总结：
1.通过CTAS或create table like方式备份或复制表时: Oracle数据库：新生成的备份表，会丢失所有索引、约束(除非空约束)。 MySQL数据库：新生成的备份表，会自动复制表对应的索引、约束。 2.表重命名后，Oracle、MySQL数据库索引、约束都正常。 3.不同表，能否使用同名约束和表空间： Oracle数据库不可以，MySQL数据库可以。 4.检查性约束 Oracle数据库：可以正常使用检查性约束。 MySQL数据库：创建检查性约束正常，没有报错，但是无法查询到已经创建的检查性约束，而且检查性约束也不起作用。 5.主键指定名称 Oracle数据库:主键可以指定名称。 MySQL数据库：主键创建时可以指定名称，但是不生效，后台只显示名称"PRIMARY"。
Oracle 数据库
---创建测试表 create table t10(id int,name char(10),age int,price int,xdesc char(20)); ---添加主键 alter table t10 add constraint pk_t10_id primary key(id); ---添加唯一约束 alter table t10 add constraint uk_t10_name unique(name); ---添加检查约束 alter table t10 add constraint ck_t10_age check(age >=18); ---添加索引 create index i_t10_price on t10(price);
插入测试数据
insert into t10 values(1,'cjc',100,1000000,'xxx'); commit; SQL> select * from t10; ID NAME AGE PRICE XDESC ---------- ---------- ---------- ---------- -------------------- 1 cjc 100 1000000 xxx
建表语句
SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10','CJC') from dual;
select dbms_metadata.get_ddl('INDEX','INDEX_NAME','INDEX_OWNER') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10','CJC')
--------------------------------------------------------------------------------
CREATE TABLE "CJC"."T10"
( "ID" NUMBER(*,0),
"NAME" CHAR(10),
"AGE" NUMBER(*,0),
"PRICE" NUMBER(*,0),
"XDESC" CHAR(20),
CONSTRAINT "PK_T10_ID" PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXT
RANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M
AXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_
CACHE DEFAULT)
TABLESPACE "CJCTBS" ENABLE,
CONSTRAINT "UK_T10_NAME" UNIQUE ("NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 C
OMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINE
XTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT C
ELL_FLASH_CACHE DEFAULT)
TABLESPACE "CJCTBS" ENABLE,
CONSTRAINT "CK_T10_AGE" CHECK (age >=18)
ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTR
ANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 M;
检查约束
set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10');
OWNER TABLE_NAME C CONSTRAINT_NAME INDEX_NAME STATUS
---------- ---------- - -------------------- --------------- --------
CJC T10 C CK_T10_AGE ENABLED
CJC T10 P PK_T10_ID PK_T10_ID ENABLED
CJC T10 U UK_T10_NAME UK_T10_NAME ENABLED
检查索引
set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10');
TABLE_OWNER TABLE_NAME COLUMN_NAM INDEX_NAME
--------------- ---------- ---------- -----------------------------------
CJC T10 ID PK_T10_ID
CJC T10 NAME UK_T10_NAME
CJC T10 PRICE I_T10_PRICE
场景一：CTAS创建表,检查索引和约束
Create table t10_bak as select * from t10; SQL> select * from t10_bak; ID NAME AGE PRICE XDESC ---------- ---------- ---------- ---------- -------------------- 1 cjc 100 1000000 xxx
建表语句
SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10_BAK','CJC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10_BAK','CJC')
--------------------------------------------------------------------------------
CREATE TABLE "CJC"."T10_BAK"
( "ID" NUMBER(*,0),
"NAME" CHAR(10),
"AGE" NUMBER(*,0),
"PRICE" NUMBER(*,0),
"XDESC" CHAR(20)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MIN
EXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CJCTBS"
检查约束，没有约束
set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10_BAK');
no rows selected
检查索引，没有索引
set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10_BAK');
no rows selected
场景二：表重命名，检查索引和约束 alter table t10 rename to t10_1; 建表语句
SET LINE 300
SET PAGESIZE 1000
SET LONG 1000
select dbms_metadata.get_ddl('TABLE','T10_1','CJC') from dual;
DBMS_METADATA.GET_DDL('TABLE','T10_1','CJC')
--------------------------------------------------------------------------------
CREATE TABLE "CJC"."T10_1"
( "ID" NUMBER(*,0),
"NAME" CHAR(10),
"AGE" NUMBER(*,0),
"PRICE" NUMBER(*,0),
"XDESC" CHAR(20),
CONSTRAINT "PK_T10_ID" PRIMARY KEY ("ID"
)
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE S
TATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLAS
H_CACHE DEFAULT)
TABLESPACE "CJCTBS" ENABLE,
CONSTRAINT "UK_T10_NAME" UNIQUE ("NAME")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENT
S 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT
CELL_FLASH_CACHE DEFAULT)
TABLESPACE "CJCTBS" ENABLE,
CONSTRAINT "CK_T10_AGE" CHECK (age >=18
) ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1
检查约束，正常
set line 300
col owner for a10
col table_name for a10
col CONSTRAINT_NAME for a20
col INDEX_NAME for a15
select OWNER,TABLE_NAME,CONSTRAINT_TYPE,CONSTRAINT_NAME,INDEX_NAME,status from dba_constraints where OWNER='CJC' and table_name in ('T10_1');
OWNER TABLE_NAME C CONSTRAINT_NAME INDEX_NAME STATUS
---------- ---------- - -------------------- --------------- --------
CJC T10_1 C CK_T10_AGE ENABLED
CJC T10_1 P PK_T10_ID PK_T10_ID ENABLED
CJC T10_1 U UK_T10_NAME UK_T10_NAME ENABLED
检查索引，正常
set line 300
set pagesize 100
col TABLE_OWNER for a15
col TABLE_NAME for a10
col COLUMN_NAME for a10
col INDEX_NAME for a35
select TABLE_OWNER,TABLE_NAME,COLUMN_NAME,INDEX_NAME from dba_ind_columns where TABLE_NAME in ('T10_1');
TABLE_OWNER TABLE_NAME COLUMN_NAM INDEX_NAME
--------------- ---------- ---------- -----------------------------------
CJC T10_1 ID PK_T10_ID
CJC T10_1 NAME UK_T10_NAME
CJC T10_1 PRICE I_T10_PRICE
场景三：不同表创建同名约束和索引
新增表 create table t10(id int,name char(10),age int,price int,xdesc char(20)); 无法添加同名约束 ---添加主键 alter table t10 add constraint pk_t10_id primary key(id); ORA-02264: name already used by an existing constraint ---添加唯一约束 alter table t10 add constraint uk_t10_name unique(name); ORA-02264: name already used by an existing constraint ---添加检查约束 alter table t10 add constraint ck_t10_age check(age >=18); ORA-02264: name already used by an existing constraint 无法添加同名索引 ---添加索引 create index i_t10_price on t10(price); ORA-00955: name is already used by an existing object
场景四：检查性约束
SQL> insert into t10_1 values(2,'chen',15,2000000,'hhh'); insert into t10_1 values(2,'chen',15,2000000,'hhh') * ERROR at line 1: ORA-02290: check constraint (CJC.CK_T10_AGE) violated
MySQL数据库测试
---创建测试表 create table t10(id int,name char(10),age int,price int,xdesc char(20)); ---添加主键 alter table t10 add constraint pk_t10_id primary key(id); ---添加唯一约束 alter table t10 add constraint uk_t10_name unique(name); ---添加检查约束 alter table t10 add constraint ck_t10_age check(age >=18); ---添加索引 alter table t10 add index i_t10_price (price);
查看建表语句
[cjctest]> show create table t10\G; *************************** 1. row *************************** Table: t10 Create Table: CREATE TABLE `t10` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, `xdesc` char(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_t10_name` (`name`), KEY `i_t10_price` (`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
插入测试数据
insert into t10 values(1,'cjc',100,1000000,'xxx'); root@localhost:mysql.sock 09:39 [cjctest]> select * from t10; +----+------+------+---------+-------+ | id | name | age | price | xdesc | +----+------+------+---------+-------+ | 1 | cjc | 100 | 1000000 | xxx | +----+------+------+---------+-------+ 1 row in set (0.00 sec)
场景一：CTAS创建表,检查索引和约束
create table t10_bak as select * from t10; ERROR 1786 (HY000): Statement violates GTID consistency: CREATE TABLE ... SELECT. 启用了GTID，需要先复制表结构，在插入数据 create table t10_bak like t10; insert into t10_bak select * from t10;
查看表结构
show create table t10_bak\G; *************************** 1. row *************************** Table: t10_bak Create Table: CREATE TABLE `t10_bak` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, `xdesc` char(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_t10_name` (`name`), KEY `i_t10_price` (`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec) show create table t10\G; *************************** 1. row *************************** Table: t10 Create Table: CREATE TABLE `t10` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, `xdesc` char(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_t10_name` (`name`), KEY `i_t10_price` (`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
查看约束
[cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name like 't10%' order by 2,3; +-------------------+------------+-----------------+-----------------+ | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-------------------+------------+-----------------+-----------------+ | cjctest | t10 | PRIMARY | PRIMARY KEY | | cjctest | t10 | uk_t10_name | UNIQUE | | cjctest | t10_bak | PRIMARY | PRIMARY KEY | | cjctest | t10_bak | uk_t10_name | UNIQUE | +-------------------+------------+-----------------+-----------------+ 4 rows in set (0.00 sec)
场景二：表重命名，检查索引和约束 alter table t10 rename t10_1; 查询表结构
root@localhost:mysql.sock 10:59 [cjctest]> show create table t10_1\G; *************************** 1. row *************************** Table: t10_1 Create Table: CREATE TABLE `t10_1` ( `id` int(11) NOT NULL, `name` char(10) DEFAULT NULL, `age` int(11) DEFAULT NULL, `price` int(11) DEFAULT NULL, `xdesc` char(20) DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `uk_t10_name` (`name`), KEY `i_t10_price` (`price`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 1 row in set (0.00 sec)
root@localhost:mysql.sock 10:59 [cjctest]> select * from t10_1; +----+------+------+---------+-------+ | id | name | age | price | xdesc | +----+------+------+---------+-------+ | 1 | cjc | 100 | 1000000 | xxx | +----+------+------+---------+-------+ 1 row in set (0.00 sec)
查看约束
[cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name = 't10_1' order by 2,3; +-------------------+------------+-----------------+-----------------+ | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-------------------+------------+-----------------+-----------------+ | cjctest | t10_1 | PRIMARY | PRIMARY KEY | | cjctest | t10_1 | uk_t10_name | UNIQUE | +-------------------+------------+-----------------+-----------------+ 2 rows in set (0.00 sec)
场景三：不同表创建同名约束和索引 MySQL 创建测试表
create table t10(id int,name char(10),age int,price int,xdesc char(20));
---添加主键 alter table t10 add constraint pk_t10_id primary key(id); ---添加唯一约束 alter table t10 add constraint uk_t10_name unique(name); ---添加检查约束 alter table t10 add constraint ck_t10_age check(age >=18); ---添加索引 alter table t10 add index i_t10_price (price);
检查约束
root@localhost:mysql.sock 11:02 [cjctest]> select CONSTRAINT_SCHEMA,TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE from information_schema.TABLE_CONSTRAINTS where CONSTRAINT_SCHEMA='cjctest' and table_name like 't10%' order by 2,3; +-------------------+------------+-----------------+-----------------+ | CONSTRAINT_SCHEMA | TABLE_NAME | CONSTRAINT_NAME | CONSTRAINT_TYPE | +-------------------+------------+-----------------+-----------------+ | cjctest | t10 | PRIMARY | PRIMARY KEY | | cjctest | t10 | uk_t10_name | UNIQUE | | cjctest | t10_1 | PRIMARY | PRIMARY KEY | | cjctest | t10_1 | uk_t10_name | UNIQUE | | cjctest | t10_bak | PRIMARY | PRIMARY KEY | | cjctest | t10_bak | uk_t10_name | UNIQUE | +-------------------+------------+-----------------+-----------------+ 6 rows in set (0.00 sec)
场景四：检查性约束
root@localhost:mysql.sock 11:09 [cjctest]> insert into t10 values(3,'ccc',1,3000000,'aaa'); Query OK, 1 row affected (0.00 sec)
###chenjuchao 20230209###
