multiple table constraints

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

multiple table constraints

Mark Wagner
The syntax for multiple table constraints seems to specify a comma between
each constraint but I noticed that no comma separator seems to be accepted
as well.

e.g. both are accepted

CREATE TABLE foo(_id primary key, x, y, unique(x), unique(y));
CREATE TABLE foo(_id primary key, x, y, unique(x) unique(y));

Just curious if this is some historical artifact or if there's some
difference between the two that I'm not aware of.

Thanks.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multiple table constraints

Richard Hipp-3
On 3/7/18, Mark Wagner <[hidden email]> wrote:
>
> e.g. both are accepted
>
> CREATE TABLE foo(_id primary key, x, y, unique(x), unique(y));
> CREATE TABLE foo(_id primary key, x, y, unique(x) unique(y));
>
> Just curious if this is some historical artifact or if there's some
> difference between the two that I'm not aware of.

This appears to be an historical artifact.

A quick spot-check shows that both forms are accepted and appear to
work going back to SQLite version 3.0.0 (2004-06-18).  But this is not
something that has been part of our test suite, so you should strive
to use only the first (correct) form.

I would fix this parser problem, except there are literally over a
trillion SQLite database files in circulation, and even if only 0.001%
of those use the incorrect second form, that still means millions of
database files out there that would break if we "fix" it.  Hence, I
won't document the second form as valid syntax, but I will add test
cases to make sure the second form continues to be accepted, to ensure
future compatibility.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multiple table constraints

Mark Wagner
Thanks for the detailed response.  I was really confused for a few minutes
this morning noticing both forms in my code.  :)

On Wed, Mar 7, 2018 at 9:44 AM, Richard Hipp <[hidden email]> wrote:

> On 3/7/18, Mark Wagner <[hidden email]> wrote:
> >
> > e.g. both are accepted
> >
> > CREATE TABLE foo(_id primary key, x, y, unique(x), unique(y));
> > CREATE TABLE foo(_id primary key, x, y, unique(x) unique(y));
> >
> > Just curious if this is some historical artifact or if there's some
> > difference between the two that I'm not aware of.
>
> This appears to be an historical artifact.
>
> A quick spot-check shows that both forms are accepted and appear to
> work going back to SQLite version 3.0.0 (2004-06-18).  But this is not
> something that has been part of our test suite, so you should strive
> to use only the first (correct) form.
>
> I would fix this parser problem, except there are literally over a
> trillion SQLite database files in circulation, and even if only 0.001%
> of those use the incorrect second form, that still means millions of
> database files out there that would break if we "fix" it.  Hence, I
> won't document the second form as valid syntax, but I will add test
> cases to make sure the second form continues to be accepted, to ensure
> future compatibility.
>
> --
> D. Richard Hipp
> [hidden email]
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

Re: multiple table constraints

David Raymond
In reply to this post by Richard Hipp-3
One wrong move will break more databases than the current human population of Earth.

No pressure, right?


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Richard Hipp
Sent: Wednesday, March 07, 2018 12:44 PM
To: SQLite mailing list
Subject: Re: [sqlite] multiple table constraints

I would fix this parser problem, except there are literally over a
trillion SQLite database files in circulation, and even if only 0.001%
of those use the incorrect second form, that still means millions of
database files out there that would break if we "fix" it.

--
D. Richard Hipp
[hidden email]
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users