Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2013-12-09
3
Medium Priority
?
442 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 4

Accepted Solution

by:
Hiran Desai earned 1400 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 600 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

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
In this series, we will discuss common questions received as a database Solutions Engineer at Percona. In this role, we speak with a wide array of MySQL and MongoDB users responsible for both extremely large and complex environments to smaller singl…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

618 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