You Asked

Hello, ASK TOM Team.

I have a parent table x (x_id, column2, column3...) and a child table y (y_id, x_id (fk from table x), column3...). Table y also is a parent table of another tables. ID column on table y is an identity.

Request: I need to remove the identity columns on tables y, z and w because the relationship on these tables is one-to-one. The new primary key would be the foreign on each one. But I also know that if I remove y_id on y table, then z and w tables would lose (couple millions of rows on these tables) the relationship with y table.

Question: what is the best step by step process to accomplish this.

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)
);

Thanks.

Regards.

and Connor said...

Can we get a test case with 3 tables

- table X
- table Y
- a child of table Y

and a clear description telling us

- which columns you want to keep
- which columns you want to remove

I'm a bit lost here on what you're asking

=============

I mean .... I really don't think it was too much to ask for you to at least test your script even ONCE :-(

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

Anyway....


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>

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Review

Geraldo, August 13, 2021 - 9:09 am UTC

Sorry, Connor, for the failed test cases. When I wrote them in that specific moment I had some limitations and had to do it by memory. Sorry again. ☹️

Thanks you for the answer.

It really helped.

Regards,

August 16, 2021 - 4:44 am UTC

No problem. I was just venting a little - no harm intended

More to Explore

Design

New to good database design? Check out Chris Saxon's full fundamentals class.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Review

More to Explore

Design

Remove foreign key column from parent table and child table

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Review

More to Explore

Design