On Thu, Aug 4, 2011 at 10:32 AM, Bob Sneidar <b...@twft.com> wrote: > Ick! Do stuff that causes SQL to throw errors. I suppose that is a way to > do it, but a simple select will do it cleanly. Keeps the SQL error logs > clean too. If multiuser, lock the SQL database first, then check, insert if > query returns 0 records, unlock the SQL database. > > All are perfectly valid solutions. Preference for one method over another should be based on reasons (if present) and personal preference otherwise.
Using a UNIQUE field can slow down INSERT operations are very large tables if you are performing mass inserts. A user table typically doesn't have this problem. Marking a field as UNIQUE prevents errors when working with the database in an interactive mode. Perhaps the OP does this (probably not, though, given the nature of the question). Doing a SELECT to lookup and then a follow-up INSERT is solving the same problem twice: search a B-tree for a given value, and search it again when determining where to insert the new key. Marking as UNIQUE allows you to do this work only once, which might be a performance win; profiling is needed. Keep in mind, it's not causing "SQL to throw errors" as in "let's create egregious code and break things." Referential integrity, transactions, uniqueness, foreign keys, these things all exist for a reason: to prevent bad things from happening now or down the road when the rules you put in place aren't quite as fresh in memory. Using those tools when appropriate is *highly* recommended. This may be one of those instances. Perhaps not. But a quick, knee-jerk reaction like "ick" is hardly a deliberate, thought-through reason to go down one path over another. I don't mean to cause a flame war, incite, or imply anything negative. I simply want the OP to not walk away from this thread thinking that UNIQUE is bad in some way, or that tossing actions at SQL that "throw errors" is a bad thing either. Look at the problem, the pros/cons of various solutions, and pick your poison wisely. Jeff M. _______________________________________________ use-livecode mailing list use-livecode@lists.runrev.com Please visit this url to subscribe, unsubscribe and manage your subscription preferences: http://lists.runrev.com/mailman/listinfo/use-livecode
