1

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

2 Answers 2

1

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

12
  • I tried "alter table student drop column course_id cascade constraints;" already, but it didn't work. It gave me a warning which says : "cannot drop column from the table owned by SYS". Aug 6, 2013 at 12:01
  • 2
    Please don't create any objects under SYS! Aug 6, 2013 at 12:07
  • @RubbalBhusri: I couldn't agree more with Colin. The SYS (and SYSTEM) account is totally off limits for "regular" work. The only situation where you need it is for DBA tasks (adding tablespaces, adding users and so on). Never ever, under no circumstance should you create normal tables (views, triggers, ...) using SYS or SYSTEM.
    – user1822
    Aug 6, 2013 at 12:10
  • 1
    @RubbalBhusri: those are not "databases" those are users. And by default there are much more. You have to createa new user (e.g. RUBBAL) and do your work with that. Again: stop using the SYS and SYSTEM account (=user) for your regular work. I urge you to read the concepts manual or any other introduction to Oracle before proceding any further.
    – user1822
    Aug 6, 2013 at 12:13
  • 1
    @RubbalBhusri: what's wrong? "cannot drop column from the table owned by SYS" for a start I'd say.
    – user1822
    Aug 6, 2013 at 13:00
0

The answer by a_horse_with_no_name should be good enough for most situations, but if you need to automate this across multiple databases where the generated constraint name may be different, you can do something like this:

declare
   constraintname varchar2(512);  
begin  
    select constraint_name
    INTO constraintname 
    from user_cons_columns
    where table_name = 'student'
    and column_name = 'course_id'
    AND position = 1;

    execute immediate 'alter table student DROP constraint ' || constraintname;
end;  
/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.