Solved

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

Posted on 2013-12-09
3
432 Views
Last Modified: 2013-12-15
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
Comment
Question by:marrowyung
3 Comments
 
LVL 4

Accepted Solution

by:
ItWorked earned 350 total points
ID: 39705565
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
 
LVL 11

Assisted Solution

by:Angelp1ay
Angelp1ay earned 150 total points
ID: 39705566
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
 
LVL 1

Author Comment

by:marrowyung
ID: 39705597
"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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
I have been using r1soft Continuous Data Protection (http://www.r1soft.com/linux-cdp/) for many years now with the mySQL Addon and wanted to share a trick I have used several times. For those of us that don't have the luxury of using all transact…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question