Support for named foreign keys?

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

Support for named foreign keys?

csanyipal
Hi,

when will SQLite support the named foreign keys?

I am using SchemaCrawler to visualize my SQLite databases and because
SQLite does not support it yet, foreign keys get automatically
generated foreign key names in the diagram.

So it would be nice if SQLite would support it one day. Is'n it?

--
Best, Pali
_______________________________________________
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: Support for named foreign keys?

Clemens Ladisch
Csányi Pál wrote:
> when will SQLite support the named foreign keys?

It does support named foreign keys:

  CREATE TABLE t (
    id PRIMARY KEY,
    k1 CONSTRAINT name_here REFERENCES t,
    k2,
    CONSTRAINT another_name FOREIGN KEY(k2) REFERENCES t
  );


Regards,
Clemens
_______________________________________________
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: Support for named foreign keys?

petern
In reply to this post by csanyipal
Definitely what Clemens said:
https://www.sqlite.org/syntax/table-constraint.html

But you probably want to see all the FK's, not merely named ones.  I
suggest parsing for the 'REFERENCES' keyword of the table schemas stored in
sqlite_master (or .schema report of the shell tool).  You can fix the
SQLite "driver" of SchemaCrawler yourself, since it is open source.
SchemaCrawler claims to already use regular expressions to parse well
formed SQL schema:

https://github.com/sualeh/SchemaCrawler

SQLite FK including anonymous ones follows the syntax "REFERENCES
foreign-table".  See

https://www.sqlite.org/syntax/foreign-key-clause.html


On Sun, Sep 24, 2017 at 11:05 AM, Csányi Pál <[hidden email]> wrote:

> Hi,
>
> when will SQLite support the named foreign keys?
>
> I am using SchemaCrawler to visualize my SQLite databases and because
> SQLite does not support it yet, foreign keys get automatically
> generated foreign key names in the diagram.
>
> So it would be nice if SQLite would support it one day. Is'n it?
>
> --
> Best, Pali
> _______________________________________________
> 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
Reply | Threaded
Open this post in threaded view
|

Re: Support for named foreign keys?

Keith Medcalf
In reply to this post by csanyipal

Why do you think this?

The syntax diagrams are quite clear that FOREIGN KEY constraints can be named.

sqlite> create table parent
   ...> (id integer primary key);
sqlite> create table child
   ...> (fk integer null,
   ...> constraint dippydoo foreign key (fk) references parent(id));
sqlite>


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Csányi Pál
>Sent: Sunday, 24 September, 2017 12:06
>To: SQlite User
>Subject: [sqlite] Support for named foreign keys?
>
>Hi,
>
>when will SQLite support the named foreign keys?
>
>I am using SchemaCrawler to visualize my SQLite databases and because
>SQLite does not support it yet, foreign keys get automatically
>generated foreign key names in the diagram.
>
>So it would be nice if SQLite would support it one day. Is'n it?
>
>--
>Best, Pali
>_______________________________________________
>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
Reply | Threaded
Open this post in threaded view
|

Re: Support for named foreign keys?

Scott Robison-2
I think he's asking for FK constraint names to be reported in conflict
messages which has been requested in the past, but not included up until
now because of the approach taken.

On Sep 24, 2017 4:16 PM, "Keith Medcalf" <[hidden email]> wrote:

>
> Why do you think this?
>
> The syntax diagrams are quite clear that FOREIGN KEY constraints can be
> named.
>
> sqlite> create table parent
>    ...> (id integer primary key);
> sqlite> create table child
>    ...> (fk integer null,
>    ...> constraint dippydoo foreign key (fk) references parent(id));
> sqlite>
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Csányi Pál
> >Sent: Sunday, 24 September, 2017 12:06
> >To: SQlite User
> >Subject: [sqlite] Support for named foreign keys?
> >
> >Hi,
> >
> >when will SQLite support the named foreign keys?
> >
> >I am using SchemaCrawler to visualize my SQLite databases and because
> >SQLite does not support it yet, foreign keys get automatically
> >generated foreign key names in the diagram.
> >
> >So it would be nice if SQLite would support it one day. Is'n it?
> >
> >--
> >Best, Pali
> >_______________________________________________
> >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
>
_______________________________________________
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: Support for named foreign keys?

Keith Medcalf

I don't think so.  The question is about interpreting the schema, not about executing anything at all.


---
The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume.


>-----Original Message-----
>From: sqlite-users [mailto:sqlite-users-
>[hidden email]] On Behalf Of Scott Robison
>Sent: Sunday, 24 September, 2017 16:47
>To: SQLite mailing list
>Subject: Re: [sqlite] Support for named foreign keys?
>
>I think he's asking for FK constraint names to be reported in
>conflict
>messages which has been requested in the past, but not included up
>until
>now because of the approach taken.
>
>On Sep 24, 2017 4:16 PM, "Keith Medcalf" <[hidden email]> wrote:
>
>>
>> Why do you think this?
>>
>> The syntax diagrams are quite clear that FOREIGN KEY constraints
>can be
>> named.
>>
>> sqlite> create table parent
>>    ...> (id integer primary key);
>> sqlite> create table child
>>    ...> (fk integer null,
>>    ...> constraint dippydoo foreign key (fk) references
>parent(id));
>> sqlite>
>>
>>
>> ---
>> The fact that there's a Highway to Hell but only a Stairway to
>Heaven says
>> a lot about anticipated traffic volume.
>>
>>
>> >-----Original Message-----
>> >From: sqlite-users [mailto:sqlite-users-
>> >[hidden email]] On Behalf Of Csányi Pál
>> >Sent: Sunday, 24 September, 2017 12:06
>> >To: SQlite User
>> >Subject: [sqlite] Support for named foreign keys?
>> >
>> >Hi,
>> >
>> >when will SQLite support the named foreign keys?
>> >
>> >I am using SchemaCrawler to visualize my SQLite databases and
>because
>> >SQLite does not support it yet, foreign keys get automatically
>> >generated foreign key names in the diagram.
>> >
>> >So it would be nice if SQLite would support it one day. Is'n it?
>> >
>> >--
>> >Best, Pali
>> >_______________________________________________
>> >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
>>
>_______________________________________________
>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
Reply | Threaded
Open this post in threaded view
|

Re: Support for named foreign keys?

Scott Robison-2
Ah, I misread. Sorry for the noise.

On Sep 24, 2017 4:59 PM, "Keith Medcalf" <[hidden email]> wrote:

>
> I don't think so.  The question is about interpreting the schema, not
> about executing anything at all.
>
>
> ---
> The fact that there's a Highway to Hell but only a Stairway to Heaven says
> a lot about anticipated traffic volume.
>
>
> >-----Original Message-----
> >From: sqlite-users [mailto:sqlite-users-
> >[hidden email]] On Behalf Of Scott Robison
> >Sent: Sunday, 24 September, 2017 16:47
> >To: SQLite mailing list
> >Subject: Re: [sqlite] Support for named foreign keys?
> >
> >I think he's asking for FK constraint names to be reported in
> >conflict
> >messages which has been requested in the past, but not included up
> >until
> >now because of the approach taken.
> >
> >On Sep 24, 2017 4:16 PM, "Keith Medcalf" <[hidden email]> wrote:
> >
> >>
> >> Why do you think this?
> >>
> >> The syntax diagrams are quite clear that FOREIGN KEY constraints
> >can be
> >> named.
> >>
> >> sqlite> create table parent
> >>    ...> (id integer primary key);
> >> sqlite> create table child
> >>    ...> (fk integer null,
> >>    ...> constraint dippydoo foreign key (fk) references
> >parent(id));
> >> sqlite>
> >>
> >>
> >> ---
> >> The fact that there's a Highway to Hell but only a Stairway to
> >Heaven says
> >> a lot about anticipated traffic volume.
> >>
> >>
> >> >-----Original Message-----
> >> >From: sqlite-users [mailto:sqlite-users-
> >> >[hidden email]] On Behalf Of Csányi Pál
> >> >Sent: Sunday, 24 September, 2017 12:06
> >> >To: SQlite User
> >> >Subject: [sqlite] Support for named foreign keys?
> >> >
> >> >Hi,
> >> >
> >> >when will SQLite support the named foreign keys?
> >> >
> >> >I am using SchemaCrawler to visualize my SQLite databases and
> >because
> >> >SQLite does not support it yet, foreign keys get automatically
> >> >generated foreign key names in the diagram.
> >> >
> >> >So it would be nice if SQLite would support it one day. Is'n it?
> >> >
> >> >--
> >> >Best, Pali
> >> >_______________________________________________
> >> >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
> >>
> >_______________________________________________
> >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
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users