CODEWITHSUNDEEP
oracle-databaseforeign-keys
Joshua
Joshua

Reputation: 26732

Changing a column in oracle that has a foreign key constraint

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.

Upvotes: 0

Views: 2051

Answers (2)

Mark Roddy
Mark Roddy

Reputation: 27936

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>

Upvotes: 6

Stack Programmer
Stack Programmer

Reputation: 3484

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.

Upvotes: 1

Related Questions