FOREING KEY constraint

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
11 messages Options
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

FOREING KEY constraint

Igor Korot
Hi, ALL,
Is SQLite supports naming a foreign key constraint?
And if it does - is there a way to check for its uniqueness?

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

Re: FOREING KEY constraint

Keith Medcalf

Yes.

CREATE TABLE Parent
(
  id INTEGER PRIMARY KEY,
  data TEXT COLLATE NOCASE,
  CONSTRAINT unique_data UNIQUE (data)
);

CREATE TABLE Child
(
  id INTEGER PRIMARY KEY,
  parent INTEGER CONSTRAINT child_to_parent REFERENCES Parent(id)
);

or

CREATE TABLE Child
(
  id INTEGER PRIMARY KEY,
  parent INTEGER,
  CONSTRAINT child_to_parent FOREIGN KEY (parent) REFERENCES Parent(id)
);

What do you mean "check for uniqueness?

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Igor Korot
> Sent: Sunday, 2 July, 2017 21:12
> To: Discussion of SQLite Database
> Subject: [sqlite] FOREING KEY constraint
>
> Hi, ALL,
> Is SQLite supports naming a foreign key constraint?
> And if it does - is there a way to check for its uniqueness?
>
> Thank you.
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Keith Medcalf
In reply to this post by Igor Korot

See https://sqlite.org/lang_createtable.html

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Igor Korot
> Sent: Sunday, 2 July, 2017 21:12
> To: Discussion of SQLite Database
> Subject: [sqlite] FOREING KEY constraint
>
> Hi, ALL,
> Is SQLite supports naming a foreign key constraint?
> And if it does - is there a way to check for its uniqueness?
>
> Thank you.
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Simon Slavin-3
In reply to this post by Keith Medcalf


On 3 Jul 2017, at 4:37am, Keith Medcalf <[hidden email]> wrote:

> What do you mean "check for uniqueness?

If you give two constraints the same name, does SQLite

A) Ignore the problem
B) Reject the second one complaining "duplicate name"
C) Replace the first one with the second

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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Keith Medcalf

From what I can tell the answer is (A).  The constraint_name is simply a comment to be reported (if possible) when the constraint is violated.

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı

> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Simon Slavin
> Sent: Monday, 3 July, 2017 04:49
> To: SQLite mailing list
> Subject: Re: [sqlite] FOREING KEY constraint
>
>
>
> On 3 Jul 2017, at 4:37am, Keith Medcalf <[hidden email]> wrote:
>
> > What do you mean "check for uniqueness?
>
> If you give two constraints the same name, does SQLite
>
> A) Ignore the problem
> B) Reject the second one complaining "duplicate name"
> C) Replace the first one with the second
>
> Simon.
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Igor Korot
Hi, Keith et al,

On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf <[hidden email]> wrote:
>
> From what I can tell the answer is (A).  The constraint_name is simply a comment to be reported (if possible) when the constraint is violated.

So is it possible to check that the foreign key with the given name
already present for a given
table?

Thank you.

>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>> -----Original Message-----
>> From: sqlite-users [mailto:[hidden email]]
>> On Behalf Of Simon Slavin
>> Sent: Monday, 3 July, 2017 04:49
>> To: SQLite mailing list
>> Subject: Re: [sqlite] FOREING KEY constraint
>>
>>
>>
>> On 3 Jul 2017, at 4:37am, Keith Medcalf <[hidden email]> wrote:
>>
>> > What do you mean "check for uniqueness?
>>
>> If you give two constraints the same name, does SQLite
>>
>> A) Ignore the problem
>> B) Reject the second one complaining "duplicate name"
>> C) Replace the first one with the second
>>
>> Simon.
>> _______________________________________________
>> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

J. King-3
The sqlite_master table should have this information.

SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND tbl_name IS your_table_name;

On July 3, 2017 9:37:04 AM EDT, Igor Korot <[hidden email]> wrote:

>Hi, Keith et al,
>
>On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf <[hidden email]>
>wrote:
>>
>> From what I can tell the answer is (A).  The constraint_name is
>simply a comment to be reported (if possible) when the constraint is
>violated.
>
>So is it possible to check that the foreign key with the given name
>already present for a given
>table?
>
>Thank you.
>
>>
>> --
>> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>>
>>> -----Original Message-----
>>> From: sqlite-users
>[mailto:[hidden email]]
>>> On Behalf Of Simon Slavin
>>> Sent: Monday, 3 July, 2017 04:49
>>> To: SQLite mailing list
>>> Subject: Re: [sqlite] FOREING KEY constraint
>>>
>>>
>>>
>>> On 3 Jul 2017, at 4:37am, Keith Medcalf <[hidden email]> wrote:
>>>
>>> > What do you mean "check for uniqueness?
>>>
>>> If you give two constraints the same name, does SQLite
>>>
>>> A) Ignore the problem
>>> B) Reject the second one complaining "duplicate name"
>>> C) Replace the first one with the second
>>>
>>> Simon.
>>> _______________________________________________
>>> 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

--
Sent from my Android device with K-9 Mail. Please excuse my brevity.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Clemens Ladisch
J. King wrote:
> The sqlite_master table should have this information.
>
> SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND tbl_name IS your_table_name;

Constraints do not have separate entries in the sqlite_master table.
And there is no other mechanism to get this information without parsing the SQL.


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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Keith Medcalf
In reply to this post by J. King-3

This is not true.  You can only get the constraints from sqlite_master by parsing the table creation sql statements.

constraints are not added independantly to sqlite_master

--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of J. King
> Sent: Monday, 3 July, 2017 07:51
> To: SQLite mailing list
> Subject: Re: [sqlite] FOREING KEY constraint
>
> The sqlite_master table should have this information.
>
> SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND
> tbl_name IS your_table_name;
>
> On July 3, 2017 9:37:04 AM EDT, Igor Korot <[hidden email]> wrote:
> >Hi, Keith et al,
> >
> >On Mon, Jul 3, 2017 at 7:13 AM, Keith Medcalf <[hidden email]>
> >wrote:
> >>
> >> From what I can tell the answer is (A).  The constraint_name is
> >simply a comment to be reported (if possible) when the constraint is
> >violated.
> >
> >So is it possible to check that the foreign key with the given name
> >already present for a given
> >table?
> >
> >Thank you.
> >
> >>
> >> --
> >> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
> >>
> >>> -----Original Message-----
> >>> From: sqlite-users
> >[mailto:[hidden email]]
> >>> On Behalf Of Simon Slavin
> >>> Sent: Monday, 3 July, 2017 04:49
> >>> To: SQLite mailing list
> >>> Subject: Re: [sqlite] FOREING KEY constraint
> >>>
> >>>
> >>>
> >>> On 3 Jul 2017, at 4:37am, Keith Medcalf <[hidden email]> wrote:
> >>>
> >>> > What do you mean "check for uniqueness?
> >>>
> >>> If you give two constraints the same name, does SQLite
> >>>
> >>> A) Ignore the problem
> >>> B) Reject the second one complaining "duplicate name"
> >>> C) Replace the first one with the second
> >>>
> >>> Simon.
> >>> _______________________________________________
> >>> 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
>
> --
> Sent from my Android device with K-9 Mail. Please excuse my brevity.
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Keith Medcalf
In reply to this post by Clemens Ladisch

You can get foreign key constraints with a pragma.
Check constraints need to parse the SQL.


--
˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı


> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of Clemens Ladisch
> Sent: Monday, 3 July, 2017 08:00
> To: [hidden email]
> Subject: Re: [sqlite] FOREING KEY constraint
>
> J. King wrote:
> > The sqlite_master table should have this information.
> >
> > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name AND
> tbl_name IS your_table_name;
>
> Constraints do not have separate entries in the sqlite_master table.
> And there is no other mechanism to get this information without parsing
> the SQL.
>
>
> Regards,
> Clemens
> _______________________________________________
> 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
|  
Report Content as Inappropriate

Re: FOREING KEY constraint

Paul Sanderson
pragma foreign_key_list(table_name) may help


Paul
www.sandersonforensics.com
skype: r3scue193
twitter: @sandersonforens
Tel +44 (0)1326 572786
http://sandersonforensics.com/forum/content.php?195-SQLite-Forensic-Toolkit
-Forensic Toolkit for SQLite
email from a work address for a fully functional demo licence

On 3 July 2017 at 15:05, Keith Medcalf <[hidden email]> wrote:

>
> You can get foreign key constraints with a pragma.
> Check constraints need to parse the SQL.
>
>
> --
> ˙uʍop-ǝpısdn sı ɹoʇıuoɯ ɹnoʎ 'sıɥʇ pɐǝɹ uɐɔ noʎ ɟı
>
>
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of Clemens Ladisch
> > Sent: Monday, 3 July, 2017 08:00
> > To: [hidden email]
> > Subject: Re: [sqlite] FOREING KEY constraint
> >
> > J. King wrote:
> > > The sqlite_master table should have this information.
> > >
> > > SELECT count() FROM sqlite_master WHERE name IS your_constraint_name
> AND
> > tbl_name IS your_table_name;
> >
> > Constraints do not have separate entries in the sqlite_master table.
> > And there is no other mechanism to get this information without parsing
> > the SQL.
> >
> >
> > Regards,
> > Clemens
> > _______________________________________________
> > 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
Loading...