SEARCH YOUR SOLUTION HERE  

Set field to default null drop foreign key


in a table I've a field like
`payment_id` CHAR(5) NOT NULL,
with fk
CONSTRAINT `order_fk_payment` FOREIGN KEY (`payment_id`) REFERENCES `payment_method` (`payment_id`) ON DELETE CASCADE ON UPDATE CASCADE

now I need to do on client request (there are customer with not payment_id)
ALTER TABLE `order`
MODIFY `payment_id` CHAR(5) default NULL,

so I'm wondering if I should get rid of the fk or not like
ALTER TABLE `order` DROP FOREIGN KEY `order_fk_payment`;

Posted On: Thursday 25th of October 2012 10:04:23 PM Total Views:  420
View Complete with Replies




Related Messages:

Apache2triad's mysql not accesible from the lan? did i miss a setting?   (146 Views)
ok, something that is stumbling on my DEV LAN here. I installed apaches triad which is apache, mysql, and php trio im sure you all know. well, i got the server working fine, but MYSQL is acting up, and i probably have it misconfigged. server is //raptor dev machine is //eagle i go to my dev machine, and type in raptor/phpmyadmin and the page loads on this page the base href is localhost, not raptor, so the stylesheet and all the links are broken... this is the 1st problem the 2nd problem is i cant get my db's to pull up in my editors on //eagle. i set the db host as either 192.168.1.199, raptor, http://raptor, and even localhost, or 127.0.0.1, and also all of them again with the :3306 appended, but nothing is mapping to the mysql server, and if it is then i am getting access issues. so i go to the priveleges section on phpmyadmin from the server and set up the priveleges for example as username root, and password 1234, grant all permissions from % instead of just localhost. but this apparently isnt sufficient. oh, another thing, im not able to ping 192.168.1.199:3306, isnt this indicative of a network problem any suggestions
Multiple parents in nested set hierarchy   (133 Views)
I have a database of destinations that I need to store in some hierarchical way and the best solution I've found is the nested set hierarchy. I need to be able to search by all descendants or all ancestors and since I don't know how to make a MySQL recursive query ( I don't think one can make a recursive query in MySQL ) - and not a recursive php method - I thought that this would be the best solution. However, there are times when I need to have multiple parents to a destination. In my example results below I have 'Peru' as a child of the 'Amazon' and 'South America', and 'Amazon' is a child of 'South America'. This works for when I need to find all descendants or ancestors, but I haven't found a good way to getting just the immediate parents/ancestors or the immediate children. I haven't even started to work with adding and removing records from the nested set yet Any guidance or suggestions would be appreciated. Code: -- Get all Ancestors SELECT d.`NAME`, a1.`lft`, a1.`rgt` FROM `destination` AS d , `destination_assoc` AS a1 , ( SELECT * FROM `destination_assoc` WHERE `NAME` = 'Cusco' ) AS a2 WHERE a1.`lft` < a2.`lft` AND a1.`rgt` > a2.`rgt` AND d.`NAME` = a1.`NAME` ORDER BY a1.`lft` -- Get immediate Ancestors -- Get all Descendants SELECT d.`NAME`, a1.`lft`, a1.`rgt` FROM `destination` AS d , `destination_assoc` AS a1 , ( SELECT * FROM `destination_assoc` WHERE `NAME` = 'Earth' ) AS a2 WHERE a1.`lft` > a2.`lft` AND a1.`rgt` < a2.`rgt` AND d.`NAME` = a1.`NAME` ORDER BY a1.`lft` -- Get immediate Descendants Code: +---------------+-----+-----+ | NAME | lft | rgt | +---------------+-----+-----+ | Earth | 0 | 27 | | North America | 1 | 4 | | Alaska | 2 | 3 | | South America | 5 | 26 | | Amazon | 6 | 11 | | Peru | 7 | 10 | | Cusco | 8 | 9 | | Ecuador | 12 | 21 | | Galapagos | 13 | 20 | | Bartolome | 14 | 15 | | Isabela | 16 | 17 | | Santa Cruz | 18 | 19 | | Peru | 22 | 25 | | Cusco | 23 | 24 | +---------------+-----+-----+ 0[Earth]27 / \ 1[North America]4 5[South America]26 / / | \ 2[Alaska]3 / | \ / | \ 6[Amazon]11 | 22[Peru]25 / 12[Ecuador]21 \ 7[Peru]10 | 23[Cusco]24 / 13[Galapagos]20 8[Cusco]9 / | \ / | 18[Santa Cruz]19 14[Bartolome]15 | 16[Isabela]17
Resetting primary key to 0 in phpmyadmin   (181 Views)
Ok I have two tables and two primary keys one on each table. My hosting saddly doesnt suport InnoDB tables so the only way i have to match records between tables is the primary keys on each table. The problem I have is resetting them both back to 0 is there any way to do this in phpmyadmin
Concatenate multiple rows into a set list(no duplicates) based on unique ID   (130 Views)
all; Any help, directions, or thoughts on this is much apprecaited. Let's say I have the following table: Code: ID Value 1 a 1 a 1 b 2 c 2 a 3 a 3 c 3 b Is there a quick way in MySQL that will let me build a new table so that it is now represented where the ValueCombination is sorted such as: Code: ID ValueCombination 1 a 2 a,c 3 a,b,c After this is done can I build a table from this tabel for the counts associated to the Values in the ValueCombination field such as: Code: Value Count a 3 b 1 c 2 After this I would like to build a table again from the ValueCombination table to count the instances of the ValueCombination such as: Code: ValueCombination Count a 1 a,b,c 1 a,c 1 As mentioned any suggestions are much appreciated. Celeste.
Mysql-connector-java-3.1.14 charset prepareStatement   (223 Views)
I've a java application using mysql 4.1 on Windows and it work right. If I run the same application on linux (Ubuntu 6.06) it has some problems. If I exec an insert statment without PreparedStatement (with an executeUpdate("insert ...") it also work right. If I exec the same statment with PreparedStatement, data wiritten into the table are trash, strings chars and numbers are crazy. It seems that PreparedStatement data conversion fails. Have you any idea
MySQL Replication Relay Resets Prematurely   (118 Views)
I read all kinds of information everywhere and cannot seem to extend the size of the replication relay file beyond 1 GB before it gets deleted. I set relay-log-space-limit=40G and max_relay_log_size=20G because I read that the relay-log-space-limit should be twice as large as max_relay_log_size. Here is what I have in my.cnf (I restarted the server): Code: [mysqld] max_allowed_packet=64M port = xxx datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Default to using old password format for compatibility with mysql 3.x # clients (those using the mysqlclient10 compatibility package). old_passwords=1 server-id = 2 master-host = xxx master-port=xxx master-user = replicant master-password = xxx log-bin = /var/log/mysql/bin.log log-bin-index = /var/log/mysql/log-bin.index log-error = /var/log/mysql/error.log relay-log = /var/log/mysql/relay.log relay-log-info-file = /var/log/mysql/relay-log.info relay-log-index = /var/log/mysql/relay-log.index relay-log-space-limit=40G max_relay_log_size=20G [mysql.server] user=mysql basedir=/var/lib [mysqld_safe] err-log=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid Surely I am missing something
LOAD DATA INFILE & datatype set   (146 Views)
I have a field "day_availability" in my database like this `day_availability` set('1','2','3','4','5','6','7') I am trying to load data to database from 'source.txt' but "day_availability" gives me problem. Code: LOAD DATA INFILE 'source.txt' INTO TABLE myTable FIELDS TERMINATED BY ','; I tried '3,4,5' then "'3','4','5'" without success. What should be the correct format Marija,Janicenoks,WI,"'2','4','5','7'",... Samuel,Solomon,MA,'3,4,5',... Thank you
MySQL charset issues   (161 Views)
I'm trying to write a fuzzer (in PHP) to see which charsets that MySQL supports are vulnerable to encoding attacks, but while its mostly working, I can't even get queries to execute for these charsets: Code: ucs2 cp866 keybcs2 macce macroman cp852 latin7 cp1251 cp1256 cp1257 binary geostd8 cp932 eucjpms I use the sql Code: Original - sql Code SET CHARSET 'ascii'
Limit results to last few in a set   (198 Views)
hello limit clause: SELECT * FROM `your_table` LIMIT 0, 5 however i want to limit it to the last 5 in the query result. in other words the last 5 starting from the the bottom of the set of results.
Mysql Dual Master setup - Triggers help pls!!   (257 Views)
Hi List,, I have setup Mysql Dual master running with 4 db's. Mysql Master1 -------------> Mysql Master2 DB
Need Parameters to set in my.cnf MYSQL   (145 Views)
Hi List,, I have Installed 5.0.27-standard on Linux box. Linux Box h/w info :- Intel(R) Xeon(TM) CPU 2.80GHz [ 4 processors] MemTotal: 2074736 kB HDD:- 160GB I have a application which needs to have 2500 connections in pool. can somone post me the wat are all the parameters i need to set in my.cnf so as to get good performance ,[ depending upon my hadrware]. We need to support 10000 concurrent users. Pls help !!!
Merging two data sets that have primary keys   (194 Views)
I am trying to merge two tables into one. These tables are the exact same structure and they both have primary keys. The numbers in these primary keys (INT) do conflict with each other. I am want to do a mysqldump on each table that can be used to merge them into one. I don't care what the primary key values are equal to after they are merged together. It can reindex the whole data set if it wants to I just don't want to get an error when I try to import the second sql file. INSERT IGNORE gets me around the error but it doesn't move all of the data in. Is there a way to either drop the primary key field out of the mysqldump or is there a way to get the server to just grab the next AUTO_INCREMENT value when there is a conflict
Advanced question: matching "set" against another "set"?   (106 Views)
Hi all Is there anyway I can test two comma-seperated lists (SETs) against each other, matching if any element is in the other set For instance, testing (2,5,7,9) against (1,2,3,4) would match because 2 is in both sets I'm using FIND_IN_SET at the moment, but I need to pass a set to the first param, which doesn't work. Any ideas
Call Stored Procedure gives "Can't return resultset in the given context"   (114 Views)
i have some Stored Procedure which i developed using "SQL Manager 2005 for MySQL" and which is compiling and executing correctly in that software. but when i call that procedure from phpmyadmin it returns the error "Can't return resultset in the given context". why this happens.. how to call and use procedures from phpmyadmin and in php programs.. i am giving following commants to call that procedure. call sample_proc() call sample_proc(@a) etc but nothing working in phpmyadmin -----My sample Procedure --------- CREATE PROCEDURE `sample_proc`(OUT param1 TEXT) DETERMINISTIC SQL SECURITY DEFINER COMMENT '' BEGIN select * from books END -----My sample Procedure ---------
Need advice on setup table   (176 Views)
I'm working on this script where users can add another users to their friends list. Basically, user add another user as friend and must be approved by another user in order to be called friend. The problem is I don't know how to setup a table for this. So If I can get some advices, that would be great.
shift a set of rows in a table (was "Sorry, I don't know what this is called")   (123 Views)
I want to shift a set of rows in a table. Well I will try and explain it graphically. image_id/sig_id/ord sig_id = 3, image_id = 30, oldOrd = 3, newOrd = 4 34 3 0 | 34 3 0 30 3 1 | 36 3 1 36 3 2 | 31 3 2 31 3 3 | 28 3 3 28 3 4 | 30 3 4 32 3 5 | 32 3 5 That is generally what I am trying to do. I am pass my PHP function parameter. I used this query to get the first list "SELECT * FROM `image2sig`WHERE `sig_id` =3 ORDER BY `ord` ASC." I can find a way or doing this with tuns of PHP query but that will get slow. I'm just asking is there a way to do this with MySQL. Basically I want to know if there is a was there I can just add or subtract 1 from a set of fields. Sorry, MySQL noob.
Bring back all data, display subset first   (189 Views)
! I have an issue pulling back data the way I need it to come back... Basically, I need to have all the data come back, but group certain data (based on a variable that is being written in PHP) and display it first. So I have tried the following with no luck: $select = "(select word,dates from sets where dates
Timestamp offset help   (155 Views)
I have a field in my database that is a timestamp. I am using phpmyadmin to manage the db. When I look at the structure of this field, I set the Browser Transformation to text/plain: date format, which makes the time stamp more readable. How do I set the time offset to make it my local time Would I put the offset in the transformation options box If so what's the format
unable to reset password on windows box   (176 Views)
trying to reset the root password on a windows 2003 server. someone directed me to a link on the mysql site the other day, giving instructions for reseting using the dos prompt and a text file. makes sense to me, except im getting an error in the dos prompt. my text file, which is to set the new password is.... Code: SET PASSWORD FOR 'root'@'localhost' = PASSWORD('xavalon'); then in the dos prompt, i type.... Code: C:\> C:\Program Files\MySQL\MySQL Server 4.1\bin\mysqld-nt --init-file=C:\mysql-init.txt then i get this error.... Code: "C:\Program" is not not recognized, im assuming because theres a space between "Program" & "Files" i am pointing to the proper folder where mysql is located. im assuming the error has to do with the space between "Program" & "Files". I just dont know. any ideas
how do I set up a link?   (186 Views)
I am having to construct a database by hand from a specification given to us by an outside client. Their table layout examples have: Field, Attribute, Type, Link. In link are (as example) MOD_CODE and MOD_TYPE - both are other tables in the database. Are these foreign keys And if so what do I need to do to set up those links