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.
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?
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…”
i want to know how to drop a unique constraint ,if that constraint have no naming?
Can anyone plz help me….?
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;
THANKS Scott for your valuable reply for my question, within 3 hours…STEREO rocks
THANKS Scott for your valuable reply for my question,
within 3hours…STEREO rocks….
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..
anisha, separate your url parameters with an ampersand (“&”). filename.php?param1=a¶m2=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.
SET foreign_key_checks = 0;
DROP table mytable;
SET foreign_key_checks = 0;
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?