CREATE FOREIGN KEY support

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

CREATE FOREIGN KEY support

Igor Korot
Hi, ALL,
I believe the creation a foreign key on the existing tables is not supported
on SQLite.

However, I can issue a series of the SQL command which will emulate
the creation of foreign key.

1. BEGIN
2. CREATE TEMPORARY TABLE temp AS SELECT * FROM <fkTable>;
3. DROP TABLE <fkTabke>;
4. CREATE TABLE <fkTable>(, FOREIGN KEY() REFERENCE pkTable() ) AS
SELECT * FROM temp;
5. DROP TABLE temp;
6. COMMIT;

My questions are:
1. On step 3 all ttriggers and indexes will be dropped as well, right?
2. If yes - what would be the best way to read them and apply to the step 4?

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
|

Re: CREATE FOREIGN KEY support

Clemens Ladisch
Igor Korot wrote:
> 3. DROP TABLE <fkTabke>;
>
> On step 3 all ttriggers and indexes will be dropped as well, right?

Yes.

> 4. CREATE TABLE <fkTable>(, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * FROM temp;
>
> what would be the best way to read [triggers and indexes] and apply to the step 4?

Run .schema in the command-line shell, and search where the table name is mentioned.

Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema
to modify the table definition in place: https://stackoverflow.com/a/42970982/11654


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: CREATE FOREIGN KEY support

Igor Korot
Hi, Clemens,

On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch <[hidden email]> wrote:

> Igor Korot wrote:
>> 3. DROP TABLE <fkTabke>;
>>
>> On step 3 all ttriggers and indexes will be dropped as well, right?
>
> Yes.
>
>> 4. CREATE TABLE <fkTable>(, FOREIGN KEY() REFERENCE pkTable() ) AS SELECT * FROM temp;
>>
>> what would be the best way to read [triggers and indexes] and apply to the step 4?
>
> Run .schema in the command-line shell, and search where the table name is mentioned.
>
> Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema
> to modify the table definition in place: https://stackoverflow.com/a/42970982/11654

It would be nice if that backdoor was implemented as "ALTER TABLE..." one.

But that aside I am trying to run this from the C interface.
I guess I can just try to implement it in my program. Just curious -
why I should feel
adventurous?

Thank you.

>
>
> 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
|

Re: CREATE FOREIGN KEY support

Igor Tandetnik-2
On 9/27/2017 9:18 AM, Igor Korot wrote:
> On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch <[hidden email]> wrote:
>> Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema
>> to modify the table definition in place: https://stackoverflow.com/a/42970982/11654
>
> Just curious -  why I should feel adventurous?

If you make a mistake, you can render the schema un-parseable and the database un-openable, effectively losing all the data in it.
--
Igor Tandetnik


_______________________________________________
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: CREATE FOREIGN KEY support

David Raymond
But then you don't have to worry about issues with foreign keys or triggers, or the time to copy data, etc. But yes it does "void your warranty" to do so.


pragma writable_schema = true;

begin; --important

update sqlite_master set sql = '..., foreign key () references blah ()...' WHERE type = 'table' AND name = 'your table name'; --entire SQL text for the table. Be absolutely sure to include type and name in the where clause.

select * from sqlite_master; --examine to make sure you didn't forget the WHERE clause and update all tables

commit;

pragma writable_schema = false;

analyze sqlite_master; --forces re-parse so your connection has the changes


-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of Igor Tandetnik
Sent: Wednesday, September 27, 2017 9:42 AM
To: [hidden email]
Subject: Re: [sqlite] CREATE FOREIGN KEY support

On 9/27/2017 9:18 AM, Igor Korot wrote:
> On Wed, Sep 27, 2017 at 7:04 AM, Clemens Ladisch <[hidden email]> wrote:
>> Alternatively, if you're feeling adventurous, you can use PRAGMA writable_schema
>> to modify the table definition in place: https://stackoverflow.com/a/42970982/11654
>
> Just curious -  why I should feel adventurous?

If you make a mistake, you can render the schema un-parseable and the database un-openable, effectively losing all the data in it.
--
Igor Tandetnik


_______________________________________________
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