On 13 Aug 2013, at 8:56am, Stephen Hughes <[email protected]> wrote:
> Thanks Stephen, Simon & Kees for your replies all of which indicate that
> I am starting from the wrong place, in that it is the built-in
> programming capabilities of Visual FoxPro that I was using rather than
> its Database Engine.
> Without wishing to digress into which is the best programming language to
> use with SQLite, does anyone know if there are any pre-written examples
> of what I'm trying to achieve? I've checked the Perl CPAN Library but
> could not find anything.
I think that, like many other programmers, you're expecting to find a short
clip of code with perhaps 5 or 10 lines which show what you want. But SQL is a
declarative language: you define your table in one line of code and that
definition tells SQLite what values are allowed and which attempts should be
rejected with an error message.
The things you asked about can all be done in a single command. Or, at least,
one command to define each table. And since they're just one line long nobody
thinks they're worth making up a clip of code with them. But going back to
your original post, and spreading each command over multiple lines so you can
see clearly what I'm doing ....
> - set MAXIMUM/MINIMUM values.
CREATE TABLE journeys (
start TEXT,
destination TEXT,
distance REAL,
CHECK (distance BETWEEN 0.0 AND 5000.0),
CHECK (start != destination))
> - allow me to force UPPER case.
CREATE TABLE airports (
designation TEXT,
fullName TEXT,
city TEXT,
country TEXT,
CHECK (designation == UPPER(designation)),
CHECK (fullName NOT NULL),
CHECK (designation != ''))
Once the tables have been defined, it's SQLite's job to make sure that your
data doesn't violate those constraints. Commands (INSERT or UPDATE) which
violate the CHECK constraints will simply fail: instead of 'SQLITE_OK' your
function call will return 'SQLITE_CONSTRAINT', and you can spot it and handle
it however you want in your programming language.
You can read about foreign keys (to allow only values which have already been
entered in a different table) here:
<http://www.sqlite.org/foreignkeys.html>
Simon.
_______________________________________________
sqlite-users mailing list
[email protected]
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
