Removing foreign key when it is not named

alter-tableforeign keyoracle

I've created a table named 'student' as :

create table student(
  stud_id number primary key, 
  stud_name varchar2(30) not null, 
  branch_id varchar2(4) references branch(branch_id),
  course_id number references course(course_id),
  hostel varchar2(4)
);

Later on, I found that I don't need course_id column, now I want to remove that column, when I tried to do that, a warning message appeared saying : "cannot drop column from the table owned by SYS" then I thought, I must remove foreign key constraint first and then drop table, but that didn't work. I used :

alter table student drop column course_id;

and since I didn't name my constraint, therefore can't use the following statement:

alter table student drop constraint;

I am using oracle database 11g

Best Answer

You can use cascade constraints to also drop any constraint related to the column:

alter table student drop column course_id cascade constraints;

More details regarding the drop column clause can be found in the manual.

If you want to find out how the constraint was actually named (e.g. if you only want to drop the constraint, not the column), you can query the system catalogs for this:

select constraint_name
from user_cons_columns
where table_name = 'STUDENT'
and column_name = 'COURSE_ID';

That will return something like SYS_C0052446 (it will be a different value on your system). And that name then can be used for the drop constraint:

alter table student drop constraint SYS_C0052446;

More details about the system catalog can be found in the manual

Related Question