Changing a column in oracle that has a foreign key constraint

Question

I have a column that is only 6 characters long in a table that references a column that is 20 characters using a foreign key constraint. How do I fix this?

Note: The issue was due to the limitation of the Oracle SQL Developer Edit table. When I performed the specific alter column, it worked fine.

Answer 1

One of the possible solution to your problem could be that you increase the size of column of 6 characters to 20 characters.

Another solution could be that you can create a dummy column in the table with the column size of 20 characters. Set the value of that column to be calculated by a function, and define a function such that the dummy column in each row is assigned a value of first six characters of 20 character column. Then use the foreign key constraint.

Answer 2

SQL> create table parent_tbl(col1 char(20) primary key);
Table created.
SQL> create table child_tbl(col1 char(6) primary key, constraint col1_fk foreign key (col1) references parent_tbl(col1));
Table created.
SQL> alter table child_tbl modify col1 char(20);
Table altered.
SQL>