On 01/03/2012, at 4:22 AM, Pete wrote: > I would like to include as much error checking as possible in my database > schema.
That's an admirable aim. The whole point of constraints is to bring the error checking as close to the data model as possible. > The problem I have is that the error messages that come back from constraint > violations are extremely generic (e.g. "constraint failed") and would mean > nothing to a user. I tried including a name for constraints hoping I could > check the error message for the name and translate it into a meaningful user > message, but the name isn't returned in the error message. Yes, this is very frustrating and reduces the effectiveness of the whole constraint and check facility. > Are there any tricks by which to get meaningful error messages when a > constraint fails? I saw the RAISE command - perhaps that could be used in a > CHECK constraint, but it feels like I would be duplicating built in > constraints if I do that, e.g CHECK (Col1 IS NOT NULL) for a NOT NULL > constraint. The "raise" command is helpful in triggers, eg: select raise(abort, 'columnValue is above maximum') where new.columnValue > maximumAllowed But you have to create triggers that duplicate the constraints that you already have in your table schema. Very error prone, inconsistent, redundant and inefficient. SQLite doesn't allow customisation the error message that raise provides, so I can show what error occurred, but not where it occurred. ie this is not allowed: select raise(abort, 'columnValue ' || new.columnValue || ' is above maximum ' || maximumAllowed || ' in row ' || new.rowid) where new.columnValue > maximumAllowed Thanks, Tom Tom Brodhurst-Hill BareFeetWare -- iPhone/iPad/iPod and Mac software development, specialising in databases develo...@barefeetware.com -- Twitter: http://twitter.com/barefeetware/ Facebook: http://www.facebook.com/BareFeetWare _______________________________________________ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
