7 Replies Latest reply: Jun 6, 2008 10:28 AM by 643619 RSS

    How to Remove Foreign Key Constraint

    619418
      Hello Freinds,

      I have created two sample tables ...

      ----------------------------------------------------------------
      create table primaryTable(id int primary key,value int);
      OK; 1 row affected

      create table childTable(id1 int primary key,id int references primaryTable(id),value int);
      OK; 1 row affected

      ants_dsn_3.4> select * from childTable;
      ---------------------------------------
      | ID1 | ID | VALUE |
      ---------------------------------------
      | 1 | 1 | 1 |
      | 2 | 2 | 1 |
      | 3 | 3 | 1 |
      ---------------------------------------
      OK; Returned 3 rows from 3 fields

      ants_dsn_3.4> select * from primaryTable;
      --------------------------+
      | ID | VALUE |
      --------------------------+
      | 1 | 1 |
      | 2 | 1 |
      | 3 | 1 |
      --------------------------+

      I want to drop the Foreign key in the Child Table.
      And when i do that i get the Following error.

      ants_dsn_3.4> alter table childTable drop ID;
      [ANTs Software][ANTs DATABASE ODBC driver][ants_dsn_3.4] Server returned error : ERROR (70007): Cannot drop the column 'ID' that has a constraint on it

      ----------------------------------------------------------------
      Freinds Can you help to solve this problem as iam new to data base side.

      Thanks in advance,
      Faizan.
        • 1. Re: How to Remove Foreign Key Constraint
          damorgan
          Post your actual statement.

          Dropping a foreign key constraint is as simple as:
          ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;
          • 2. Re: How to Remove Foreign Key Constraint
            619418
            Dear Morgan,

            This is the actual statement while creating table
            i did not give the constraint name ...

            so i dont know how this statement works.
            "ALTER TABLE <table_name> DROP CONSTRAINT <constraint_name>;

            "

            Please see the create statements below:
            create table primaryTable(id int primary key,value int);
            OK; 1 row affected

            create table childTable(id1 int primary key,id int references primaryTable(id),value int);
            OK; 1 row affected


            i want to drop the Foreign key in the Child Table.
            And when i do that i get the Following error.

            ants_dsn_3.4> alter table childTable drop ID;
            [ANTs Software][ANTs DATABASE ODBC driver][ants_dsn_3.4] Server returned error : ERROR (70007): Cannot drop the column 'ID' that has a constraint on it
            • 3. Re: How to Remove Foreign Key Constraint
              Girish Sharma
              select constraint_name from user_constraints where table_name='Your_Table_Name' and Constraint_type='R';

              It will return you the name of foreign key constraint on that table.

              alter table Your_Table_Name drop constraint <Output Name of Above Query>;
              • 4. Re: How to Remove Foreign Key Constraint
                619418
                Hello Freind,

                I have done as u said i get one more proble here.

                select constraint_name from user_constraints where table_name='childTable' and Constraint_type='R';

                I get the Following Error.

                [ANTs Software][ANTs DATABASE ODBC driver][ants_dsn_3.4] Server returned error : ERROR (3): Table 'USER_CONSTRAINTS' does not exist in schema 'FAIZAN'!

                I dont know why this error is coming.
                • 5. Re: How to Remove Foreign Key Constraint
                  damorgan
                  The reason you are having a problem is that you are using a very bad technique for creating your constraints.
                  SQL> create table primaryTable(id int primary key,value int);

                  Table created.

                  SQL> create table childTable(id1 int primary key,id int references primaryTable(id),value int);

                  Table created.

                  SQL> select constraint_name, constraint_type
                    2  from user_constraints
                    3  where table_name = 'CHILDTABLE';

                  CONSTRAINT_NAME                C
                  ------------------------------ -
                  SYS_C0011508                   P
                  SYS_C0011509                   R

                  SQL> ALTER TABLE childtable DROP CONSTRAINT SYS_C0011509;

                  Table altered.

                  SQL>
                  Create your constraints properly and you won't get system generated names.
                  CREATE TABLE primaryTable(id INTEGER, value INTEGER);

                  ALTER TABLE primarytable
                  ADD CONSTRAINT pk_primarytable
                  PRIMARY KEY (id)
                  USING INDEX;
                  And columns named ID and VALUE are enough to make me refuse to hire someone for a job. Get a copy of Joe Celko's books and learn how to name columns. Also do not use reserved words for column names:
                  SELECT keyword
                  FROM gv$reserved_words;
                  Now lets get back to your referential constraint:
                  CREATE TABLE childTable(id1 INTEGER, id INTEGER, value INTEGER);

                  ALTER TABLE childtable
                  ADD CONSTRAINT pk_childtable
                  PRIMARY KEY (id)
                  USING INDEX;

                  ALTER TABLE childtable
                  ADD CONSTRAINT fk_child_primary_id
                  FOREIGN KEY (id)
                  REFERENCING primarytable(id);
                  BTW: I would fail a first quarter student who, on their midterm, wrote what you've written above. You should find yourself a good beginning book on database design.
                  • 6. Re: How to Remove Foreign Key Constraint
                    619418
                    Dear Morgan,
                    Thanks For your Help.Now , i know what mistake i was making in Constraints.

                    Regards,
                    Faizan
                    • 7. Re: How to Remove Foreign Key Constraint
                      643619
                      You can also temporarily disable the foreign key by using something like

                      ALTER TABLE table_name
                      disable CONSTRAINT constraint_name;

                      and then enable it when you want by

                      ALTER TABLE table_name
                      enable CONSTRAINT constraint_name;