Solved

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

Posted on 2013-12-09
3
426 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

Suggested Solutions

Fore-Foreword Today (2016) Maxmind has a new approach to the distribution of its data sets.  This article may be obsolete.  Instead of using the examples here, have a look at the MaxMind API (https://www.maxmind.com/en/geolite2-developer-package). …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This Micro Tutorial will give you a basic overview how to record your screen with Microsoft Expression Encoder. This program is still free and open for the public to download. This will be demonstrated using Microsoft Expression Encoder 4.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…

776 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