Constraints must be defined last?

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

Constraints must be defined last?

J Decker
Is there something about SQL that requires constraints to follow all column
definitions?


sqlite> create table `option4_blobs` (`option_id` char(36) default '0',
CONSTRAINT `value_id` UNIQUE (`option_id`),`binary` blob, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
Error: near "`binary`": syntax error

sqlite> create table `option4_blobs` (`option_id` char(36) default
'0',`binary` blob, CONSTRAINT `value_id` UNIQUE (`option_id`), FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON
DELETE CASCADE)
   ...> ;
-- works
_______________________________________________
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: Constraints must be defined last?

Simon Slavin-3
On 11 Apr 2018, at 6:41pm, J Decker <[hidden email]> wrote:

> Is there something about SQL that requires constraints to follow all column
> definitions?

I don't know if it applies to SQL in general, but it is in SQLite:

<https://sqlite.org/syntax/create-table-stmt.html>

The table constraints come after the column definitions.

Simon.
_______________________________________________
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: Constraints must be defined last?

Lifepillar
On 11/04/2018 19:45, Simon Slavin wrote:

> On 11 Apr 2018, at 6:41pm, J Decker <[hidden email]> wrote:
>
>> Is there something about SQL that requires constraints to follow all column
>> definitions?
>
> I don't know if it applies to SQL in general, but it is in SQLite:
>
> <https://sqlite.org/syntax/create-table-stmt.html>
>
> The table constraints come after the column definitions.

That is a SQLite restriction. According to the SQL standard,
column definitions and table constraints can appear in any order.
In fact, PostgreSQL accepts the following:

   create table T (
     constraint foo check (x > 0),
     x integer primary key
   );

Life.

_______________________________________________
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: [EXTERNAL] Constraints must be defined last?

Hick Gunter
In reply to this post by J Decker
Yes. See syntax diagram in http://sqlite.org/lang_createtable.html.

-----Urspr√ľngliche Nachricht-----
Von: sqlite-users [mailto:[hidden email]] Im Auftrag von J Decker
Gesendet: Mittwoch, 11. April 2018 19:41
An: General Discussion of SQLite Database <[hidden email]>
Betreff: [EXTERNAL] [sqlite] Constraints must be defined last?

Is there something about SQL that requires constraints to follow all column definitions?


sqlite> create table `option4_blobs` (`option_id` char(36) default '0',
CONSTRAINT `value_id` UNIQUE (`option_id`),`binary` blob, FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
   ...> ;
Error: near "`binary`": syntax error

sqlite> create table `option4_blobs` (`option_id` char(36) default
'0',`binary` blob, CONSTRAINT `value_id` UNIQUE (`option_id`), FOREIGN KEY
(`option_id`) REFERENCES `option4_map`(`option_id`)ON UPDATE CASCADE ON DELETE CASCADE)
   ...> ;
-- works
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


___________________________________________
 Gunter Hick | Software Engineer | Scientific Games International GmbH | Klitschgasse 2-4, A-1130 Vienna | FN 157284 a, HG Wien, DVR: 0430013 | (O) +43 1 80100 - 0

May be privileged. May be confidential. Please delete if not the addressee.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users