Sqlite and foreign keys

Other Databases - related issues
greeneye
Posts: 3
Joined: Thu Aug 04, 2011 9:50 pm

Sqlite and foreign keys

Post by greeneye » Thu Aug 04, 2011 10:05 pm

I've make a model with foreign keys and I've tried to export to a sqlite database.

The tables creation was correct:

Code: Select all

CREATE TABLE articoli ( 
	id INT NOT NULL,
	barcode VARCHAR( 10 ) NULL,
	descrizione VARCHAR( 20 ) NULL,
	taglia VARCHAR( 12 ) NULL,
	colore VARCHAR( 12 ) NULL,
	posizione VARCHAR( 15 ) NULL,
	prezzo_acq NUMERIC( 8, 2 ) NULL,
	prezzo_ven NUMERIC( 8, 2 ) NULL,
CONSTRAINT Pk_articoli PRIMARY KEY ( id )
 );
but the foreign key creation not:

Code: Select all

ALTER TABLE articoli ADD FOREIGN KEY fk_articoli_iva ( id ) REFERENCES iva( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE articoli ADD FOREIGN KEY fk_articoli_produttore ( id ) REFERENCES produttore( id ) ON DELETE CASCADE ON UPDATE CASCADE;

ALTER TABLE articoli ADD FOREIGN KEY fk_articoli_famiglia ( id ) REFERENCES famiglia( id ) ON DELETE CASCADE ON UPDATE CASCADE;
This is the result:
java.sql.SQLException: near "FOREIGN": syntax error
I'm not an expert but sqlite do not recognize this ALTER TABLE sintax.


Regards
Giovanni Bergamaschi

greeneye
Posts: 3
Joined: Thu Aug 04, 2011 9:50 pm

Re: Sqlite and foreign keys

Post by greeneye » Thu Aug 04, 2011 10:28 pm

I've write this code to fix the table's generation

Code: Select all

PRAGMA foreign_keys = ON;
              
CREATE TABLE articoli ( 
                	id INT NOT NULL,
                	barcode VARCHAR( 10 ) NULL,
                	descrizione VARCHAR( 20 ) NULL,
                	taglia VARCHAR( 12 ) NULL,
                	colore VARCHAR( 12 ) NULL,
                	posizione VARCHAR( 15 ) NULL,
                	prezzo_acq NUMERIC( 8, 2 ) NULL,
                	prezzo_ven NUMERIC( 8, 2 ) NULL,
                	fk_iva INT NULL,
                	fk_produttore INT NULL,
                	fk_famiglia INT NULL,
                	prezzo_acq_ass BOOL NULL,
                    CONSTRAINT Pk_articoli PRIMARY KEY ( id ),
                    FOREIGN KEY( fk_iva) REFERENCES iva(id),
                    FOREIGN KEY( fk_produttore) REFERENCES produttore(id),
                    FOREIGN KEY( fk_famiglia) REFERENCES famiglia(id)
                 );

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Sqlite and foreign keys

Post by donose.mihai » Fri Aug 05, 2011 8:27 am

greeneye wrote:I've write this code to fix the table's generation

Code: Select all

PRAGMA foreign_keys = ON;
              
CREATE TABLE articoli ( 
                	id INT NOT NULL,
                	barcode VARCHAR( 10 ) NULL,
                	descrizione VARCHAR( 20 ) NULL,
                	taglia VARCHAR( 12 ) NULL,
                	colore VARCHAR( 12 ) NULL,
                	posizione VARCHAR( 15 ) NULL,
                	prezzo_acq NUMERIC( 8, 2 ) NULL,
                	prezzo_ven NUMERIC( 8, 2 ) NULL,
                	fk_iva INT NULL,
                	fk_produttore INT NULL,
                	fk_famiglia INT NULL,
                	prezzo_acq_ass BOOL NULL,
                    CONSTRAINT Pk_articoli PRIMARY KEY ( id ),
                    FOREIGN KEY( fk_iva) REFERENCES iva(id),
                    FOREIGN KEY( fk_produttore) REFERENCES produttore(id),
                    FOREIGN KEY( fk_famiglia) REFERENCES famiglia(id)
                 );

Thanks for your feedback Greeneye, the issue will be resolved soon. we're on it.

wuweird
Posts: 2
Joined: Mon Sep 26, 2011 7:14 am

Re: Sqlite and foreign keys

Post by wuweird » Mon Sep 26, 2011 7:19 am

Has this been resolved in recent versions?

I've imported an existing sqlite database and no foreign keys are showing up, either in the layout visualisation or in the side browser. Trying to add one gives the same 'near "FOREIGN" : syntax error' result.

Thanks.

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Sqlite and foreign keys

Post by donose.mihai » Mon Sep 26, 2011 7:30 am

wuweird wrote:Has this been resolved in recent versions?

I've imported an existing sqlite database and no foreign keys are showing up, either in the layout visualisation or in the side browser. Trying to add one gives the same 'near "FOREIGN" : syntax error' result.

Thanks.

Hi,

I'll check with our software engineers and post a replay later on.

Thanks

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Sqlite and foreign keys

Post by donose.mihai » Mon Sep 26, 2011 1:58 pm

wuweird wrote:Has this been resolved in recent versions?

I've imported an existing sqlite database and no foreign keys are showing up, either in the layout visualisation or in the side browser. Trying to add one gives the same 'near "FOREIGN" : syntax error' result.

Thanks.

As promised i got the replay from our software engineers.
Our team is trying to contact the Sqlite team because some modification to the driver or table structure need to be made in order to list the FK in dbschema.
If you know somebody in the sqlite team maybe you can help us to speed up the process.
Thanks.

Mihai

wuweird
Posts: 2
Joined: Mon Sep 26, 2011 7:14 am

Re: Sqlite and foreign keys

Post by wuweird » Tue Sep 27, 2011 1:18 am

donose.mihai wrote:If you know somebody in the sqlite team maybe you can help us to speed up the process.
I wish I did :) Thanks for the quick response, though.

In case it helps, I should mention that I'm running DbSchema under Windows 7 64-bit.

Cheers.

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Sqlite and foreign keys

Post by donose.mihai » Tue Sep 27, 2011 12:30 pm

Thanks for letting us know.
If you have other issues regarding dbschema please let us know.

Thanks

firionicable
Posts: 25
Joined: Wed Mar 14, 2012 8:01 am

Re: Sqlite and foreign keys

Post by firionicable » Mon Mar 19, 2012 8:57 am

What do you exactly mean by foreign keys? Sorry for asking a dumb question...

donose.mihai
Posts: 470
Joined: Thu Apr 07, 2011 11:05 am

Re: Sqlite and foreign keys

Post by donose.mihai » Mon Mar 19, 2012 1:15 pm

firionicable wrote:What do you exactly mean by foreign keys? Sorry for asking a dumb question...
Here you can find all the info about Foreign keys.

http://en.wikipedia.org/wiki/Foreign_key

Post Reply