• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 443
  • Last Modified:

how to drop key, constraint and foreign key of a table.

Dear all,


right now I have a table have the schema like that:

CREATE TABLE `RESOURCE_ATTRIBUTE_REL` (
  `RESOURCE_ID` bigint(19) NOT NULL,
  `RESOURCE_ATTRIBUTE_ID` bigint(19) NOT NULL,
  PRIMARY KEY (`RESOURCE_ID`,`RESOURCE_ATTRIBUTE_ID`),
  KEY `FK_RES_ATTR_REL_RES_ID` (`RESOURCE_ID`),
  KEY `FK_RES_ATTR_REL_RES_ATTR_ID` (`RESOURCE_ATTRIBUTE_ID`),
  CONSTRAINT `FK_RES_ATTR_REL_RES_ATTR_ID` FOREIGN KEY (`RESOURCE_ATTRIBUTE_ID`) REFERENCES `RESOURCE_ATTRIBUTE` (`RESOURCE_ATTRIBUTE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_RES_ATTR_REL_RES_ID` FOREIGN KEY (`RESOURCE_ID`) REFERENCES `RESOURCE` (`RESOURCE_ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Open in new window


now:

1) the KEY is the index, right?
this table only for data storing purpose and we don't need this kind of thing.
only for select.
2) how to drop that key using script ?
3) how to drop constraint and foreight key theng ?
0
marrowyung
Asked:
marrowyung
2 Solutions
 
Hiran DesaiSolution ArchitectCommented:
You need DDL 'ALTER' statement to make change in table schema.

ALTER TABLE Table_Name
DROP PRIMARY KEY

Open in new window


for foreign key

ALTER TABLE Table_Name
DROP FOREIGN KEY FK_Name

Open in new window

0
 
Angelp1ayCommented:
Edit: Sorry I didn't see MySql. For MySQL ItWorked is correct:
ALTER TABLE [TABLE_NAME] DROP PRIMARY KEY

Open in new window

Edit: For MS SQL it's the same to drop either primary key or other constraints, for MySQL this is only for other constraints:
ALTER TABLE [TABLE_NAME] DROP CONSTRAINT [YOUR_CONSTRAINT_NAME]

Open in new window


Keys are not just indexes though, they also ensure some rule about your data. Your primary key for example must be unique and non-null. Foreign keys must exist in the foreign table.

Keys will have associated indexes with increase the performance of your SELECTs.
0
 
marrowyungAuthor Commented:
"ALTER TABLE Table_Name
DROP PRIMARY KEY "


I already this this one by the 'KEY' defination still there.

I heard that:

ALTER TABLE Table_Name
DROP FOREIGN KEY FK_Name 

Open in new window



will handle the contraint as this will drop the contraint too, right?

so no need this :

ALTER TABLE [TABLE_NAME] DROP CONSTRAINT [YOUR_CONSTRAINT_NAME] 

Open in new window

?

one thing is :

1) how to find out foreight key, contraint exist?
2) how fo find out 'key' is here?

this one can't tell:

 select * FROM INFORMATION_SCHEMA.tables 
    WHERE TABLE_SCHEMA = '<DB>' AND TABLE_NAME = 'table'  and COLUMN_KEY= "PRI";  ;

Open in new window


then which one call tell ?
0

Featured Post

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now