Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
add comment

1 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

share|improve this answer
    
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". –  Rubbal Bhusri Aug 6 '13 at 12:01
2  
Please don't create any objects under SYS! –  Colin 't Hart Aug 6 '13 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. –  a_horse_with_no_name Aug 6 '13 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. –  a_horse_with_no_name Aug 6 '13 at 12:13
1  
@RubbalBhusri: what's wrong? "cannot drop column from the table owned by SYS" for a start I'd say. –  a_horse_with_no_name Aug 6 '13 at 13:00
show 7 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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