Solved

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

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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Question has a verified solution.

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

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php (http://lmgtfy.com?q=how+easy+is+php)  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikiped…
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…
As a trusted technology advisor to your customers you are likely getting the daily question of, ‘should I put this in the cloud?’ As customer demands for cloud services increases, companies will see a shift from traditional buying patterns to new…

910 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now