Re: How to Use Cascade Delete Properly

LTER TABLE `jobsearchtwodb`.`jobposts` ADD CONSTRAINT `adsourcefk` FOREIGN KEY `adsourcefk` (`adsource_id`) REFERENCES `adsource` (`adsource_id`) ON DELETE RESTRICT ON UPDATE NO ACTION; ON DELETE RESTRICT having the behavior like ON DELETE CONSTRAIN? -- Lola J. Lee Beno - ColdFusion

How to Use Cascade Delete Properly

happen. I can't use triggers because for some reason I can't get the triggers working properly. Is there any way I can do this on the database side without having to write code in the application code I'm working on? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer f

Re: Trying to Create a Trigger

SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'if @counted >= 1 then SET dummy = 'Cannot delete this record' end if' at line 1 Query = if @counted >= 1 then SET dummy = 'Cannot delete this record' end i

Re: Trying to Create a Trigger

h a JOIN clause and to put your strings inside single quotes as per SQL standard, double quotes are really for object names. Right . . . I'll get the JOIN clause figured out after I figure out what's causing the above error message. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designe

Re: Trying to Create a Trigger

and adsource tables BEFORE proceeding to delete the record. If so, what's the best way to do this? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ "In rivers, the water that you touch is the last of

Re: Trying to Create a Trigger

"id"? Maybe Yes. Jobpost_id. No, not a typo. It's primary key, jobposts.adsource_id is foreign key while adsource.adsource_id is primary key. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/b

Re: Trying to Create a Trigger

eting jobpost record - that's what I'm trying to do and thus is what this trigger is supposed to do. -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ "In rivers, the water that you touch is the las

Trying to Create a Trigger

t delete this record" end if; end // delimiter ; When I run the set query without the "and old.jobpost_id" line, it runs correctly. So the syntax problem is elsewhere, but where? -- Lola J. Lee Beno - ColdFusion Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http:/

Re: Date Field Reverting to 0000-00-00 Format

Michael Stassen wrote: So, take a look at yarn_date.txt and let us know. Yes, you're right . . . there was an extra tab stop. When I deleted the extra tab, the date field were retained successfully. Thanks! -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.l

Re: Date Field Reverting to 0000-00-00 Format

Michael Stassen wrote: Just a quick reply for now . . . Ummm, if you delete the numbers to the *left* of the decimal point, "2005-01-15 10:15:42.41837" will turn into ".41837", which is still not a valid datetime. You need to delete the numbers to the *right* of the decimal point (which I ex

Date Field Reverting to 0000-00-00 Format

I have a bunch of data where one of the columns is a date field. Here's a sample of the data that I have: 1 4 14 150 1 0 0 0 1 0 2005-01-15 10:15:42.41837 2 8 15 120 1 0 0 0 2

Re: Loading Data File into Database Table

Lola J. Lee Beno wrote: ERROR 13 (HY000): Can't get stat of '/users/lolajl/documents/development/knitlib/datafiles/standardwttype.txt' (Errcode: 13) Never mind . . . I figured that I needed to add LOCAL to the query. Should have gone back to the manual page for LOAD

Re: Loading Data File into Database Table

Peter Brawley wrote: >ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your >MySQL server version for the right syntax to use near 'standardwttype.txt` Use single quotes not (dreaded) backticks. This seems to have fixed one problem. Now, I ha

Loading Data File into Database Table

I'm trying to import a set of data into a database (MySQL 5.0.17-max). Here's the query that I tried to run: LOAD DATA INFILE `standardwttype.txt` INTO TABLE StandardWeightType FIELDS TERMINATED BY `\t` LINES TERMINATED BY `\r` (standard_wt_type_id, standard_wt_desc, standard_wt_lud); And here'

Re: Can't Create Foreign Key Constraints

Gleb Paharenko wrote: Hello. The query which is works is: Thanks - just what I needed. Looks like I'll need to be extra careful with sql scripts generated from Mysql Workbench, which is still alpha right now. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www

Re: Can't Create Foreign Key Constraints

Peter Brawley wrote: Lola, />And got this following error message: >ERROR 1072 (42000): Key column '(not null)' doesn't exist in table / Yes, it's telling you what;s wrong: to define a constraint on a key, the table def must first define the key. Looking over the script again, RatingID is b

Re: Can't Create Foreign Key Constraints

Michael Stassen wrote: 1) I'm not sure what you are intending with "(`(not null)`)" in the middle of your foreign key definition, but that isn't valid mysql syntax. See the manual for the correct syntax . This is f

Can't Create Foreign Key Constraints

I'm trying to create foreign key constraints and keep getting an error message 1005 (I did look it up, but didn't see an obvious solution to fixing this for my database). The version I'm using is 5.0.17-max. I used Mysql WorkBench to create the database schema and had it generate the sql scri