问题描述
你好，问汤姆队。
我有一个父表x （ x_id、列2、列3... ）和一个子表y （ y_id、x_id (表x的fk ） ) ，列3...。表y也是另一个表的父表。表y上的ID列是一个标识。
请求：我需要删除表y、z和w上的标识列，因为这些表上的关系是一对一的。新的主键将是每个主键上的外键。但我也知道，如果删除y表上的y_id,z和w表将失去（这些表上几百万行）与y表的关系。
问题：什么是最好的一步一步的过程来完成这一点。
Test cases:
谢谢你。
致以问候。
我有一个父表x （ x_id、列2、列3... ）和一个子表y （ y_id、x_id (表x的fk ） ) ，列3...。表y也是另一个表的父表。表y上的ID列是一个标识。
请求：我需要删除表y、z和w上的标识列，因为这些表上的关系是一对一的。新的主键将是每个主键上的外键。但我也知道，如果删除y表上的y_id,z和w表将失去（这些表上几百万行）与y表的关系。
问题：什么是最好的一步一步的过程来完成这一点。
Test cases:
CREATE TABLE x(
x_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
column2 VARCHAR2(50) NOT NULL,
column3 number(6,2) NOT NULL,
PRIMARY KEY(x_id)
);
CREATE TABLE y(
y_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
x_id NUMBER NOT NULL,
column2 VARCHAR2(50) NOT NULL,
column3 int(6,2) NOT NULL,
PRIMARY KEY(y_id),
CONSTRAINT fk_x_y
FOREIGN KEY (x_id) --> one to one relationship
REFERENCES x(x_id)
);
CREATE TABLE z(
z_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
y_id NUMBER NOT NULL,
column3 VARCHAR2(30) NOT NULL,
column4 int(6,2) NOT NULL,
PRIMARY KEY(z_id),
CONSTRAINT fk_y_z
FOREIGN KEY (y_id) --> one to one relationship
REFERENCES y(y_id)
);
CREATE TABLE w(
w_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
y_id NUMBER NOT NULL,
column2 VARCHAR2(30) NOT NULL,
column3 int(6,2) NOT NULL,
column4 VARCHAR2(45) NOT NULL,
PRIMARY KEY(w_id),
CONSTRAINT fk_y_w
FOREIGN KEY (y_id) --> one to one relationship
REFERENCES y(y_id)
);
谢谢你。
致以问候。
专家解答
我们能得到一个有3张表的测试用例吗
-表X
-表Y
-表Y的子项
一个清晰的描述告诉我们
-要保留哪些列
-要删除哪些列
我有点不明白你的要求
=============
我是说...我真的不认为要求你至少测试你的脚本，哪怕只测试一次： - (
总之...
-表X
-表Y
-表Y的子项
一个清晰的描述告诉我们
-要保留哪些列
-要删除哪些列
我有点不明白你的要求
=============
我是说...我真的不认为要求你至少测试你的脚本，哪怕只测试一次： - (
SQL> CREATE TABLE t_x(
2 x_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 column2 VARCHAR2(50) NOT NULL,
4 column3 number(6,2) NOT NULL,
5 PRIMARY KEY(x_id)
6 );
Table created.
SQL>
SQL> CREATE TABLE t_y(
2 y_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 x_id NUMBER NOT NULL,
4 column2 VARCHAR2(50) NOT NULL,
5 column3 int(6,2) NOT NULL,
6 PRIMARY KEY(y_id),
7 CONSTRAINT fk_x_y
8 FOREIGN KEY (x_id) --> one to one relationship
9 REFERENCES t_x(x_id)
10 );
column3 int(6,2) NOT NULL,
*
ERROR at line 5:
ORA-00907: missing right parenthesis
SQL>
SQL> CREATE TABLE t_z(
2 z_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 y_id NUMBER NOT NULL,
4 column3 VARCHAR2(30) NOT NULL,
5 column4 int(6,2) NOT NULL,
6 PRIMARY KEY(z_id),
7 CONSTRAINT fk_y_z
8 FOREIGN KEY (y_id) --> one to one relationship
9 REFERENCES t_y(y_id)
10 );
column4 int(6,2) NOT NULL,
*
ERROR at line 5:
ORA-00907: missing right parenthesis
SQL>
SQL> CREATE TABLE t_w(
2 w_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 y_id NUMBER NOT NULL,
4 column2 VARCHAR2(30) NOT NULL,
5 column3 int(6,2) NOT NULL,
6 column4 VARCHAR2(45) NOT NULL,
7 PRIMARY KEY(w_id),
8 CONSTRAINT fk_y_w
9 FOREIGN KEY (y_id) --> one to one relationship
10 REFERENCES t_y(y_id)
11 );
column3 int(6,2) NOT NULL,
*
ERROR at line 5:
ORA-00907: missing right parenthesis总之...
SQL>
SQL> CREATE TABLE t_x(
2 x_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 column2 VARCHAR2(50) NOT NULL,
4 column3 number(6,2) NOT NULL,
5 PRIMARY KEY(x_id)
6 );
Table created.
SQL>
SQL> CREATE TABLE t_y(
2 y_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 x_id NUMBER NOT NULL,
4 column2 VARCHAR2(50) NOT NULL,
5 column3 number(6,2) NOT NULL,
6 PRIMARY KEY(y_id),
7 CONSTRAINT fk_x_y
8 FOREIGN KEY (x_id) --> one to one relationship
9 REFERENCES t_x(x_id)
10 );
Table created.
SQL>
SQL> CREATE TABLE t_z(
2 z_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 y_id NUMBER NOT NULL,
4 column3 VARCHAR2(30) NOT NULL,
5 column4 number(6,2) NOT NULL,
6 PRIMARY KEY(z_id),
7 CONSTRAINT fk_y_z
8 FOREIGN KEY (y_id) --> one to one relationship
9 REFERENCES t_y(y_id)
10 );
Table created.
SQL>
SQL> CREATE TABLE t_w(
2 w_id NUMBER GENERATED BY DEFAULT AS IDENTITY,
3 y_id NUMBER NOT NULL,
4 column2 VARCHAR2(30) NOT NULL,
5 column3 number(6,2) NOT NULL,
6 column4 VARCHAR2(45) NOT NULL,
7 PRIMARY KEY(w_id),
8 CONSTRAINT fk_y_w
9 FOREIGN KEY (y_id) --> one to one relationship
10 REFERENCES t_y(y_id)
11 );
Table created.
SQL>
SQL> insert into t_x (column2,column3) values ('x',1);
1 row created.
SQL> insert into t_y (x_id,column2,column3) values (1,'x',1);
1 row created.
SQL> insert into t_z (y_id,column3,column4) values (1,'x',1);
1 row created.
SQL> insert into t_w (y_id,column2,column3,column4) values (1,'x',1,'x');
1 row created.
SQL>
SQL> select * from t_x;
X_ID COLUMN2 COLUMN3
---------- -------------------------------------------------- ----------
1 x 1
SQL> select * from t_y;
Y_ID X_ID COLUMN2 COLUMN3
---------- ---------- -------------------------------------------------- ----------
1 1 x 1
SQL> select * from t_z;
Z_ID Y_ID COLUMN3 COLUMN4
---------- ---------- ------------------------------ ----------
1 1 x 1
SQL> select * from t_w;
W_ID Y_ID COLUMN2 COLUMN3 COLUMN4
---------- ---------- ------------------------------ ---------- ---------------------------------------------
1 1 x 1 x
SQL>
SQL> alter table t_w add x_id number;
Table altered.
SQL> alter table t_z add x_id number;
Table altered.
SQL>
SQL> update t_w set x_id =
2 ( select x_id from t_y
3 where y_id = t_w.y_id );
1 row updated.
SQL>
SQL> update t_z set x_id =
2 ( select x_id from t_y
3 where y_id = t_z.y_id );
1 row updated.
SQL>
SQL> alter table t_w drop primary key;
Table altered.
SQL> alter table t_w add primary key (x_id );
Table altered.
SQL> alter table t_w drop constraint fk_y_w;
Table altered.
SQL> alter table t_w add constraint fk_y_w foreign key ( x_id) references t_x ( x_id);
Table altered.
SQL> alter table t_w set unused column w_id;
Table altered.
SQL> alter table t_w set unused column y_id;
Table altered.
SQL>
SQL> alter table t_z drop primary key;
Table altered.
SQL> alter table t_z add primary key (x_id );
Table altered.
SQL> alter table t_z drop constraint fk_y_z;
Table altered.
SQL> alter table t_z add constraint fk_y_z foreign key ( x_id) references t_x ( x_id);
Table altered.
SQL> alter table t_z set unused column z_id;
Table altered.
SQL> alter table t_z set unused column y_id;
Table altered.
SQL>
SQL>
SQL> alter table t_y drop primary key;
Table altered.
SQL> alter table t_y add primary key (x_id );
Table altered.
SQL> alter table t_y drop constraint fk_x_y;
Table altered.
SQL> alter table t_y add constraint fk_x_y foreign key ( x_id) references t_x ( x_id);
Table altered.
SQL> alter table t_y set unused column y_id;
Table altered.
SQL> desc t_x
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X_ID NOT NULL NUMBER
COLUMN2 NOT NULL VARCHAR2(50)
COLUMN3 NOT NULL NUMBER(6,2)
SQL> desc t_y
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
X_ID NOT NULL NUMBER
COLUMN2 NOT NULL VARCHAR2(50)
COLUMN3 NOT NULL NUMBER(6,2)
SQL> desc t_z
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
COLUMN3 NOT NULL VARCHAR2(30)
COLUMN4 NOT NULL NUMBER(6,2)
X_ID NOT NULL NUMBER
SQL> desc t_w
Name Null? Type
----------------------------------------------------------------------- -------- ------------------------------------------------
COLUMN2 NOT NULL VARCHAR2(30)
COLUMN3 NOT NULL NUMBER(6,2)
COLUMN4 NOT NULL VARCHAR2(45)
X_ID NOT NULL NUMBER
SQL>
SQL>
文章转载自askTom，如果涉嫌侵权，请发送邮件至：contact@modb.pro进行举报，并提供相关证据，一经查实，墨天轮将立刻删除相关内容。
