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:  436
View Complete with Replies

RELATED TOPICS OF MYSQL PROGRAMMING LANGUAGE




Apache2triad's mysql not accesible from the lan? did i miss a setting?

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
VIEWS ON THIS POST

153

Posted on:

Wednesday 24th October 2012
View Replies!

Multiple parents in nested set hierarchy

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 \t\t\t -- 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 \t\t\t\t\t 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
VIEWS ON THIS POST

138

Posted on:

Wednesday 24th October 2012
View Replies!

Resetting primary key to 0 in phpmyadmin

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
VIEWS ON THIS POST

189

Posted on:

Wednesday 24th October 2012
View Replies!

Concatenate multiple rows into a set list(no duplicates) based on unique ID

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.
VIEWS ON THIS POST

136

Posted on:

Wednesday 24th October 2012
View Replies!

Mysql-connector-java-3.1.14 charset prepareStatement

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
VIEWS ON THIS POST

230

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL Replication Relay Resets Prematurely

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
VIEWS ON THIS POST

124

Posted on:

Wednesday 24th October 2012
View Replies!

LOAD DATA INFILE & datatype set

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
VIEWS ON THIS POST

159

Posted on:

Wednesday 24th October 2012
View Replies!

MySQL charset issues

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'
VIEWS ON THIS POST

183

Posted on:

Wednesday 24th October 2012
View Replies!

Limit results to last few in a set

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.
VIEWS ON THIS POST

214

Posted on:

Wednesday 24th October 2012
View Replies!

Mysql Dual Master setup - Triggers help pls!!

Hi List,, I have setup Mysql Dual master running with 4 db's. Mysql Master1 -------------> Mysql Master2 DB
VIEWS ON THIS POST

266

Posted on:

Wednesday 24th October 2012
View Replies!

Need Parameters to set in my.cnf MYSQL

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 !!!
VIEWS ON THIS POST

150

Posted on:

Wednesday 24th October 2012
View Replies!

Merging two data sets that have primary keys

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
VIEWS ON THIS POST

201

Posted on:

Wednesday 24th October 2012
View Replies!

Advanced question: matching "set" against another "set"?

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
VIEWS ON THIS POST

109

Posted on:

Wednesday 24th October 2012
View Replies!

Call Stored Procedure gives "Can't return resultset in the given context"

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 ---------
VIEWS ON THIS POST

119

Posted on:

Wednesday 24th October 2012
View Replies!

Need advice on setup table

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.
VIEWS ON THIS POST

183

Posted on:

Wednesday 24th October 2012
View Replies!

shift a set of rows in a table (was "Sorry, I don't know what this is called")

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.
VIEWS ON THIS POST

128

Posted on:

Wednesday 24th October 2012
View Replies!

Bring back all data, display subset first

! 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
VIEWS ON THIS POST

198

Posted on:

Wednesday 24th October 2012
View Replies!

Timestamp offset help

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
VIEWS ON THIS POST

162

Posted on:

Wednesday 24th October 2012
View Replies!

unable to reset password on windows box

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
VIEWS ON THIS POST

184

Posted on:

Wednesday 24th October 2012
View Replies!

how do I set up a link?

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
VIEWS ON THIS POST

193

Posted on:

Wednesday 24th October 2012
View Replies!