CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Rajeev Singh
Rajeev Singh

Reputation: 3332

How to change the size of a column with FOREIGN KEY constraint?

There are two tables

DEPT (
    DEPT_ID NUMBER(5) PRIMARY KEY,
    DEPT_NAME VARCHAR2(10)
);

COURSE (
    COURSE_ID NUMBER(5) PRIMARY KEY,
    COURSE_NAME VARCHAR2(15),DEPT_ID NUMBER(5),
    FOREIGN KEY(DEPT_ID) REFERENCES DEPT
)

I want to change the size equal to 5 of the column DEPT_ID which has a FOREIGN KEY constraint.

I tried changing but it gives error:

ORA-02267: column type incompatible with referenced column type

Which is because it violates the foreign key constraint.

I didn't supplied any name to the foreign key while creating tables. So how can I do it without dropping any table.??

Upvotes: 3

Views: 7689

Answers (3)

TheRising
TheRising

Reputation: 3

As suggested by others, you need to drop the foreign key constraint. As for the name of constraint, i know solution for 'sql server' for oracle below link may help. Display names of all constraints for a table in Oracle SQL

Upvotes: 0

S. Cassidy
S. Cassidy

Reputation: 61

You have to drop the foreign key constraint first. Then execute the command you mentioned. Then add back the constraint.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269503

I think you need to do the following:

  • drop the foreign key constraints to the tables (you can use alter table drop constraint).
  • change the data types in all the tables (you can use alter table modify column)
  • add the foreign key constraints back (alter table add constraint)

This doesn't require dropping any tables.

Upvotes: 5

Related Questions