3

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.??

Rajeev Singh
  • 3,292
  • 2
  • 19
  • 30

3 Answers3

5

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I used the command `Alter table course DROP CONSTRAINT foreign key;` but it gives error `invalid ALTER TABLE option` !!. I didn't give any name to foreign key during table creation. – Rajeev Singh Apr 03 '16 at 14:46
  • @RajeevSingh . . . If you didn't give an explicit name, you have to search through the constraints to find the right name for the foreign key. There is one; it is just gibberish. – Gordon Linoff Apr 04 '16 at 02:29
0

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

S. Cassidy
  • 61
  • 1
  • 5
0

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

Community
  • 1
  • 1
TheRising
  • 3
  • 2