0

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

Rubbal Bhusri
  • 69
  • 1
  • 4
  • 15
  • As a workaround, you can download some sort of database GUI (RazorSQL for example), use that to connect to your database, then use that to list your constraints. – Chris Forrence Aug 06 '13 at 12:44
  • Also, [this question](http://stackoverflow.com/questions/11879217/display-all-names-of-constraints-for-a-table-in-oracle-sql) might help – Chris Forrence Aug 06 '13 at 12:46
  • @ChrisForrence, Isn't there any simple solution ? – Rubbal Bhusri Aug 06 '13 at 12:46
  • Had you take a look at the accepted answer for the existing question that I posted? It looks like it lets you see the constraints on your table, so you _would_ be able to name it for your DROP CONSTRAINT call – Chris Forrence Aug 06 '13 at 12:49
  • yes I took a look, and run the code : select * from user_cons_columns where table_name = 'student'; But it showed "no rows selected" warning. – Rubbal Bhusri Aug 06 '13 at 13:02
  • Ok. As another thought, are you running in the same user account as when you created the table? It looks like you had created the table while using the SYS account. Have you been running the SELECT script/alter scripts from the same account? – Chris Forrence Aug 06 '13 at 13:22
  • @ChrisForrence, I am beginner and I don't know much about making new user accounts and assigning them privileges, I am practicing making tables under the default users i.e. SYS and SYSTEM. and right now, I don't know exactly if it's under SYS or SYSTEM. One more thing, When I wrote : "alter table student drop column course_id;" , A warning came saying : "cannot drop column from the table owned by SYS" , From this warning we can clearly guess, that the tables are created under SYS. – Rubbal Bhusri Aug 06 '13 at 13:49
  • possible duplicate of [Removing foreign key , when no constraint is given](http://stackoverflow.com/questions/18075101/removing-foreign-key-when-no-constraint-is-given) – ypercubeᵀᴹ Aug 08 '13 at 15:01

0 Answers0