Re: How to Use Cascade Delete Properly
Ian Simpson wrote: If you want deletes to be blocked, then you shouldn't be using ON DELETE CASCADE; the default behaviour is ON DELETE CONSTRAIN, which seems to be what you want; it will refuse to delete any rows that are depended on by rows in other tables. Wouldn't that be: ALTER 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 Programmer/Web Designer for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ No greater injury can be done to any youth than to let him feel that because he belongs to this or that race he will be advanced in life regardless of his own merits or efforts. - Booker T. Washington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
How to Use Cascade Delete Properly
I'm trying to understand how to use cascade delete properly but not sure if I have this backwards or not. Here's an example: I have two tables: mysql describe adsource; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | adsource_id | varchar(35) | NO | PRI | NULL| | | company_id | varchar(35) | YES | MUL | NULL| | | location| varchar(50) | YES | | NULL| | | url | varchar(200) | YES | | NULL| | +-+--+--+-+-+---+ mysql describe jobposts; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | jobpost_id | varchar(35) | NO | PRI | NULL| | | company_id | varchar(35) | NO | MUL | NULL| | | details | text | YES | | NULL| | | job_title| varchar(50) | YES | | NULL| | | postdate | date | YES | | NULL| | | salary | decimal(5,2) | YES | | NULL| | | deadlinedate | date | YES | | NULL| | | adsource_id | varchar(35) | YES | MUL | NULL| | +--+--+--+-+-+---+ For jobposts; I have adsourcefk referencing adsource.adsource_id with cascade delete set. For adsource, I have companyfk referencing company.company_id with cascade delete set. Now, say I have three jobposts records that have one referenced adsource record. If I delete one jobposts record, there now remains two jobposts records. If I delete the adsource record, the two jobposts records get deleted. I don't want this happening. What I want to have happen is: if I try to delete an adsource record and there are jobposts records containing that id as foreign key, I want the delete to NOT 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 for Hire http://www.lolajl.net/resume | Blog at http://www.lolajl.net/blog/ No greater injury can be done to any youth than to let him feel that because he belongs to this or that race he will be advanced in life regardless of his own merits or efforts. - Booker T. Washington -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Trying to Create a Trigger
I'm trying to create a trigger (5.0.45) and I've read the documentation at mysql.com. I keep getting a syntax error, but can't figure out what the error is. Here's the trigger I'm trying to create: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot 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://www.lolajl.net/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
David Giragosian wrote: I'm no expert, but 'old' is a table, I'm guessing, and it isn't referenced in the 'from' clause of the query. could it be that simple...? David No . . . 'old' is a virtual table that is the same as the table I'm doing work on. See http://dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html. What I need to do is check if jobposts record has jobposts.adsource_id (foreign key) which still exists in adsource table (primary key). If adsource record still exists, then do not proceed with deleting 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 last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Jim Lyons wrote: can you not use referential integrity for this - assuming the tables are or can be made to be innodb? The tables are myISAM. These could be changed to innodb but I want to see if i can get this trigger work. Does the jobposts table have a jobpost_id field, or is it just 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/blog/ In rivers, the water that you touch is the last of what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Query = set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ) It looks like I can't do what I want to do . . . get the jobpost_id to check jobpost 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 what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Martijn Tonies wrote: Hi, What is the exact error message? Here's the latest query: delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and old.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = Cannot delete this record; end if; end // delimiter ; Here's the error message: ERROR: Unknown column 'old.jobpost_id' in 'where clause' Works fine here (although with different tables). Just for fun and giggles, have you tried OLD. (uppercase?) Okay . . . I tried OLD. delimiter // create trigger jobposts_control before delete on jobposts for each row begin declare dummy varchar(255); set @counted = ( select count(ad.adsource_id) from adsource ad, jobposts jp where ad.adsource_id = jp.adsource_id and OLD.jobpost_id = jp.jobpost_id ); if @counted = 1 then SET dummy = 'Cannot delete this record'; end if; end // delimiter; Now I just have this error message: ERROR: 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 'end // delimiter' at line 1 Query = end // delimiter As a complete sidenote: It's better to write your JOINs with 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 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 what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trying to Create a Trigger
Andy Shellam wrote: I'm guessing it's the first semi-colon in your IF statement. Does this work...? if @counted = 1 then SET dummy = 'Cannot delete this record' end if; Just a guess! Andy. That's not it, unfortunately. ERROR: 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 '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 if -- 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 what has passed and the first of that which comes; so with present time. - Leonardo da Vinci (1452-1519) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 expect you meant), *and* you need to delete the decimal point. Then you'll have a valid datetime (e.g. 2005-01-15 10:15:42). I meant to say to the *right*, including the decimal point. I still get the date column set to -00-00 when I upload the edited file. Here's an example of one of the tables where the date was retained successfully, with the string to the right of the decimal point being trimmed: 1 Cobweb 2005-01-13 15:21:50.654149 2 Lace Weight 2005-01-13 15:21:50.654149 3 Sock2005-01-13 15:21:50.654149 And the CREATE query for this table: CREATE TABLE StandardWeightType ( standard_wt_type_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_desc varchar(50) NULL, standard_wt_lud datetime NULL, PRIMARY KEY (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; select * from standardweighttype: +-+--+-+ | standard_wt_type_id | standard_wt_desc | standard_wt_lud | +-+--+-+ | 1 | Cobweb | 2005-01-13 15:21:50 | | 2 | Lace Weight | 2005-01-13 15:21:50 | | 3 | Sock | 2005-01-13 15:21:50 | | 4 | Fingering| 2005-01-13 15:21:50 | | 5 | Baby | 2005-01-13 15:21:50 | | 6 | Sport| 2005-01-13 15:21:50 | | 7 | Double Knitting | 2005-01-13 15:21:50 | | 8 | Worsted | 2005-01-13 15:21:50 | | 9 | Aran | 2005-01-13 15:21:50 | +-+--+-+ I'm not sure why it worked for this table and not the Yarn table. I'll try out the proposed solutions later today. I do need to be able to use the date column since I will be inserting new records into the database via a web application that I will be developing; I am moving the data over from a PostgreSQL database which I'd developed earlier since I've decided to concentrate on MySQL for the time being. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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's a sample of the data that I'm trying to load: 1 Cobweb 2005-01-13 15:21:50.654149 2 Lace Weight 2005-01-13 15:21:50.654149 3 Sock2005-01-13 15:21:50.654149 4 Fingering 2005-01-13 15:21:50.654149 5 Baby2005-01-13 15:21:50.654149 There is a tab between each field, and each line terminates with \r, I believe (I use OSX). I keep getting an error message: 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` I've tried running just the first two line of the query and gotten an error message. I checked the online manual to make sure my syntax is correct, and checked The Definitive Guide to MySQL 5. How do I make this work? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 have another problem: ERROR 13 (HY000): Can't get stat of '/users/lolajl/documents/development/knitlib/datafiles/standardwttype.txt' (Errcode: 13) Here's the modified query (once I figured that I needed to specify the exact path where the file is located): LOAD DATA INFILE '/users/lolajl/documents/development/knitlib/datafiles/standardwttype.txt' INTO TABLE StandardWeightType FIELDS TERMINATED BY '\t' LINES TERMINATED BY '\r' (standard_wt_type_id, standard_wt_desc, standard_wt_lud); -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 DATA. -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 0 2005-01-15 10:22:37.756594 3 6 16 350 2 0 0 0 4 0 2005-01-15 10:27:26.559838 When I run this query: LOAD DATA LOCAL INFILE '/users/lolajl/documents/development/knitlib/datafiles/yarn_date.txt' INTO TABLE yarn (yarn_id, standard_wt_type_id, brand_id, yarn_yardage, mfr_id, yarn_meters, yarn_putup, yarn_wt_g, yarn_wt_oz, yarn_discontinued_flg, yarn_lud); The dates all get set to: -00-00 00:00:00 As you can see: | 1 | 4 |14 | 150 | 1 | 0 | 0| 0 | 1 |0 | -00-00 00:00:00 | | 2 | 8 |15 | 120 | 1 | 0 | 0| 0 | 2 |0 | -00-00 00:00:00 | | 3 | 6 |16 | 350 | 2 | 0 | 0| 0 | 4 |0 | -00-00 00:00:00 | (I've deleted as many spaces as I could so as to make this more readable.) When I delete the numbers to the left of the decimal point in the date field in yarn_date.txt, it still gets set to the above format. I tried setting the yarn_lud column to NULL and still the same thing. Here is the query that creates this table: CREATE TABLE Yarn ( yarn_id int UNSIGNED NOT NULL AUTO_INCREMENT, standard_wt_type_id int UNSIGNED NULL, brand_idint UNSIGNED NULL, yarn_yardage int NULL, mfr_id int UNSIGNED NULL, yarn_meters int NULL, yarn_putup varchar(35) NULL, yarn_wt_gint NULL, yarn_wt_oz int NULL, yarn_discontinued_flg tinyint NULL, yarn_lud datetime NULL, PRIMARY KEY (yarn_id), CONSTRAINT `fk_yarn_brandid` FOREIGN KEY (brand_id) REFERENCES Brand (brand_id), CONSTRAINT `fk_yarn_mfrid` FOREIGN KEY (mfr_id) REFERENCES Manufacturer (mfr_id), CONSTRAINT `fk_yarn_stwgttypid` FOREIGN KEY (standard_wt_type_id) REFERENCES StandardWeightType (standard_wt_type_id) )ENGINE=MyISAM DEFAULT CHARSET=utf8; The odd thing is that I have three tables with a column for the date and the dates are retained properly. What could be causing the dates to be converted to the -00-00 format automatically? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 script. I created a table as such: CREATE TABLE `ows`.`FilmsRatings` ( `RatingID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `Rating` VARCHAR(50) NULL, PRIMARY KEY (`RatingID`) ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; Then, I created another table as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; And got this following error message: ERROR 1072 (42000): Key column '(not null)' doesn't exist in table I tried again, this time deleting the CONSTRAINTs details. Then I tried to alter the table as such: mysql alter table films - add constraint fk_films_ratings - foreign key (RatingID) references FilmsRatings (RatingID); Which produced this error message: ERROR 1005 (HY000): Can't create table './ows/#sql-a8_11.frm' (errno: 150) mysql drop table films; This database is being run with InnoDB engine, so I should be able to create the foreign key constraints. So why is this happening? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html. This is from the script that was generated using Mysql Workbench, 1.0.3-alpha. I tried it with (null) and (not null); neither worked. 2) In FilmsRatings, RatingID is defined as INT UNSIGNED, but in Films it is an INT. The manual says I then modified the query as such: CREATE TABLE `ows`.`Films` ( `FilmID` INT(11) UNSIGNED NOT NULL AUTO_INCREMENT, `MovieTitle` TEXT NULL, `PitchText` TEXT NULL, `AmountBudgeted` DECIMAL(11, 0) NULL, `RatingID` INT(11) UNSIGNED NULL, `Summary` LONGTEXT NULL, `ImageName` VARCHAR(50) NULL, `DateInTheaters` DATETIME NULL, PRIMARY KEY (`FilmID`), CONSTRAINT `FK_RatingID` FOREIGN KEY `RatingID` (`(not null)`) REFERENCES `ows`.`FilmsRatings` (`RatingID`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE = InnoDB CHARACTER SET utf8 COLLATE utf8_general_ci; No dice. 3) Again quoting the manual, You can use SHOW ENGINE INNODB STATUS to display a detailed explanation of the most recent InnoDB foreign key error in the server. Which gives me: LATEST FOREIGN KEY ERROR 060207 8:33:49 Error in foreign key constraint of table ows/#sql-a8_11: foreign key (RatingID) references FilmsRatings (RatingID): Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html for correct foreign key definition. Which leads me back to the same URL that you gave me. so, it looks like I should create an index for FilmsRatings first, and then create the table Films - is that correct? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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 being defined, first in FilmsRatings which I ran to create this table and then in Films, the create table script which didn't work. How do I make the table def first define the key explicitly? Or am I missing something obvious? -- Lola - mailto:[EMAIL PROTECTED] http://www.lolajl.net | Blog at http://www.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
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.lolajl.net/blog/ Freedom is not free. I'm in Bowie, MD, USA, halfway between DC and Annapolis. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
