SUBSCRIBE VIA RSS


Subscribe to our feed

Symfony Experts

Symfony Experts
If you have an urgent question for a symfony-related issue, this is the place to ask.

Topics

Stack Overflow


The old fashioned way

RECENT TUNES

March 4, 2010 – 8:31pm MySQL alter table drop constraint…

I often forget how to modify a MySQL table to drop/add foreign key constraints. I usually think it’s:

ALTER TABLE `tablename` 
DROP CONSTRAINT`my_FK_1`

The proper syntax is “DROP FOREIGN KEY”. I typically use it like this:

ALTER TABLE `tablename`
DROP FOREIGN KEY `my_FK_1`,
ADD CONSTRAINT `my_new_FK`
	FOREIGN KEY (`column_in_this_table`)
	REFERENCES `other_table` (`other_table_pk_column`)
	ON DELETE SET NULL

via MySQL :: MySQL 5.1 Reference Manual :: 12.1.7 ALTER TABLE Syntax.

Posted by in  Web Development   |  

11 Responses to MySQL alter table drop constraint…

  1. I continue to learn from your weblog, Scott. Any reason why the latter syntax is preferable to the first? Does the first throw an error?

  2. Scott Meves says:

    Yes, the first isn’t valid and throws an error. It’s odd that to create a foreign key, the syntax is “…add constraint…” but to drop it, the syntax is “…drop foreign key…”

  3. anisha says:

    i want to know how to drop a unique constraint ,if that constraint have no naming?
    Can anyone plz help me….?

  4. Scott Meves says:

    To find out the symbol value when you want to drop a foreign key, use the SHOW CREATE TABLE statement. For example:

    mysql> SHOW CREATE TABLE your_table_name;

  5. anisha says:

    THANKS Scott for your valuable reply for my question, within 3 hours…STEREO rocks

  6. anisha says:

    THANKS Scott for your valuable reply for my question,
    within 3hours…STEREO rocks….

  7. anisha says:

    Hi Scott,hope u answer all my questions..and thanks in advance..
    How to pass two ids or two variable values from one page into another by using php?
    Option 1: (Using GET)
    Page 1: <a href="filename.php?id1=id2=”>Page 2
    Page 2: $service_id = $_GET['id1'];
    $date = $_GET['id2'];
    echo $service_id;
    echo $date;
    Is this correct or not??
    If my service_id=1245 and date=1888-02-06 in page1 then
    i got an answer like this
    …1245id2=1888-02-06…in the next page. It is the result of echo $service_id;and there is no result for echo $date;Also i got some warning and notices in page2..

  8. Scott Meves says:

    anisha, separate your url parameters with an ampersand (“&”). filename.php?param1=a&param2=b. You should consider doing some PHP tutorials online to assist or posting your questions to stackoverflow.com. Also checkout http://www.w3schools.com/php/php_get.asp for more information on URL parameters.

  9. SadSack says:

    SET foreign_key_checks = 0;

    DROP table mytable;

    SET foreign_key_checks = 0;

  10. Bullkyker says:

    The drop foreign key info was exactly what I was looking for. I was looking to change a value in a column that was the primary key and attached to a foreign key in another table. Really I was looking to change the values in both tables to maintain the relationship.

    Is there another way to do that without dropping the constraint, updating the values, adding back the constraint?

  11. rajesh paul says:

    how to delete all duplicate rows in a table?

    Is there a way except the following two solutions-
    1.ALTER IGNORE TABLE tab ADD UNIQUE (attr1,attr2,..);
    2.creating a new table taking the distinct rows from the existent table; deleting the existent table and renaming the new table.

Leave a Reply

Your email address will not be published. Required fields are marked *

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>