Problem in removing constraint.....

[apanimesh061]

create table items_avail
(
  pro_id varchar2(10) primary key,
  mod_id varchar2(5),
  comp_name varchar2(25)
);

I wish to remove the primary key constraint......and add make the prod_id and mod_id as primary keys together. i.e. primary key(prod_id, mod_id)

But he thing is this table is already connected to three more tables by foreign keys.......Please tell me if there is a way !!!!!!
Thanks in advance.
Plz reply ASAP....

[debasisdas]

to drop the constraints you need to break the connenction to other tables.

[apanimesh061]

And for that do I have to truncate all the connected tables....!!!!!

[Perry31]

create table items_avail
(
  pro_id varchar2(10) primary key,
  mod_id varchar2(5),
  comp_name varchar2(25)
);

I wish to remove the primary key constraint......and add make the prod_id and mod_id as primary keys together. i.e. primary key(prod_id, mod_id)

But he thing is this table is already connected to three more tables by foreign keys.......Please tell me if there is a way !!!!!!
Thanks in advance.
Plz reply ASAP....

Hi apanimesh,

First drop primary constrain of prod_id by using the alter command.First find the constrain name using below option

1. Use the below statement to see the constraint of table_name

select constraint_name, table_name from user_constraints where table_name = 'items_avail';

[*]Try to insert a duplicate value to the Prod_id.The moment you try to insert a duplicate value it will give unique or primary_key constraint error along with the constrain name.Note the constrain name and Use the constrain name to delete the constraint from the table by

ALTER TABLE items_avail DROP CONSTRAINT PRIMARY KEY;

After delete the constraint use the below to add constraint

ALTER TABLE items_avail ADD CONSTRAINT PK_prod_id_mod_id PRIMARY KEY(pro_id,mod_id);

[*]Or delete all the records in the table and alter the table with the below statement

ALTER TABLE items_avail MODIFY PRIMARY KEY(pro_id  mod_id);