Jim McAtee wrote: > From: "Jochem van Dieten" <[EMAIL PROTECTED]> >>Jim McAtee wrote: >> >>>Any advice or gotchas we should be aware of? Things that worked >>>in Access that won't work in MySQL? >> >>transactions >>subqueries >>foreign keys >>decimal datatype >>NOT NULL constraints > > > Are you certain about the NOT NULL constraints? I'm aware of all the others.
Instead of rejecting rows with a NULL the default value for that field is inserted (their might be a toggle for that, but IIRC only for statements that are only a single query). > We'll probably move to 4.0.14 with InnoDB tables soon, so will have > transactions and foreign key constraints at that point. You will have transactions, but you won't have real foreign keys. It is a common misconception that what MySQL implemented are foreign keys, but they are not. A foreign key indicates a one-to-many relationship, while what MySQL implemented is a many-to-many relationship. For example: CREATE TABLE master (mID INTEGER, mFKEY INTEGER); CREATE TABLE slave (sID INTEGER, sFKEY INTEGER REFERENCES master (mFKEY)); In SQL this will fail because foreign keys are only allowed to reference fields that have a unique constraint. In MySQL this is allowed and people think they have a real foreign key. But suppose there are 2 entries with the same value in the mFKEY field and one is changed, should a the entries in slave be changed in cascade as well? If people misinterpret what MySQL implemented for real foreign keys like they work in every other database and the standard defines them, their data is in serious jeopardy. > One thing we just noticed... MySQL recognizes C-style character escape > sequences, beginning with backslashes. > > http://www.mysql.com/doc/en/String_syntax.html > > For instance > > SET foo = 'some \t text' > > inserts a tab in the middle of the string. This is biting us in migrating data > fields containing backslashes. If not escaped (\\) the ODBC driver throws an > error. Anyone figured out an easy workaround? cfqueryparam Jochem ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~| Archives: http://www.houseoffusion.com/cf_lists/index.cfm?forumid=4 Subscription: http://www.houseoffusion.com/cf_lists/index.cfm?method=subscribe&forumid=4 FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=89.70.4
