Re: [sqlite] Foreign key on different database - possible?

[Simon Slavin]

On 20 Oct 2010, at 12:11pm, Frank Millman wrote:

> I am writing a generalised business/accounting application, which is
> multi-company. I have got it working using MS SQL Server and PostgreSQL as
> databases, and now I want to add sqlite3 as a third option, for small
> single-user systems and for demo purposes. In the first two DBMS's, I use
> the concept of a 'schema' to represent the different companies. They are all
> in the same database, but I can refer to individual tables using
> 'schema.tablename' notation.
> 
> sqlite3 does not seem to have the equivalent of a schema in the same sense
> as the other two, but I have got close by using the concept of separate
> databases. So far this is the first time I have bumped my head. If this is
> the only restriction, I can live with it.

Although you can find your familiar 'dot' notation in SQLite, the specialist 
use of it that SQLite makes (integrating data from two different files on disk) 
makes foreign key implementation impossible.  I suggest you use underlines 
instead of dots in your code, and keep your data in the same database file.  Or 
if the data comes from two different disk files, you would have to read it 
temporarily into one big file before doing your heavy processing.

Simon.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Frank Millman]

Drake Wilson wrote:
> 
> Quoth Frank Millman , on 2010-10-20 
> 11:47:06 +0200:
> > Ok, thanks.
> > 
> > Is there any chance of it being considered for a future release?
> 
> Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign
> key".
> 

Thanks, Drake. I found the following quote, which I assume is what you were
referring to -

"The same goes for foreign key constraints. There are severe implementation
difficulties trying to get this to work across separate database. If you
have a foreign key in a separate database, that really argues that the two
databases ought to be one."

I can understand that.

For the record, I will explain what I am doing, in case someone can suggest
an alternative approach. If not, I can live with it.

I am writing a generalised business/accounting application, which is
multi-company. I have got it working using MS SQL Server and PostgreSQL as
databases, and now I want to add sqlite3 as a third option, for small
single-user systems and for demo purposes. In the first two DBMS's, I use
the concept of a 'schema' to represent the different companies. They are all
in the same database, but I can refer to individual tables using
'schema.tablename' notation.

sqlite3 does not seem to have the equivalent of a schema in the same sense
as the other two, but I have got close by using the concept of separate
databases. So far this is the first time I have bumped my head. If this is
the only restriction, I can live with it.

The link above refers to a similar restriction with 'triggers', but at this
stage I am only making limited use of triggers, and not cross-company, so
hopefully that will not become a problem.

If I find more problems, I may have to reconsider my options. I hope not, as
I am really enjoying working with sqlite3.

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Drake Wilson]

Quoth Frank Millman , on 2010-10-20 11:47:06 +0200:
> Ok, thanks.
> 
> Is there any chance of it being considered for a future release?

Search http://www.sqlite.org/cvstrac/wiki?p=SqliteWikiFaq for "foreign
key".

> Frank

   ---> Drake Wilson
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Dan Kennedy]

On Oct 20, 2010, at 4:47 PM, Frank Millman wrote:

> Dan Kennedy wrote:
>>
>> On Oct 20, 2010, at 3:59 PM, Frank Millman wrote:
>>
>>> Hi all
>>>
>>> I am using SQLite 3.7.2 on Fedora 10.
>>>
>>> I have multiple databases, which I can access concurrently
>> by using
>>> the
>>> 'attach' command, and then referring to each table using
>>> 'database.tablename'. It works well.
>>>
>>> Now I want to create a foreign key constraint where the
>> parent table
>>> is in
>>> one database and the child table is in another database. However,
>>> when I use
>>> the syntax 'REFERENCES database.tablename' I get a syntax error.
>>>
>>> Is it possible to do what I want?
>>
>> No. It is not.
>>
>
> Ok, thanks.
>
> Is there any chance of it being considered for a future release?

Seems quite unlikely, unfortunately.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Frank Millman]

Dan Kennedy wrote:
> 
> On Oct 20, 2010, at 3:59 PM, Frank Millman wrote:
> 
> > Hi all
> >
> > I am using SQLite 3.7.2 on Fedora 10.
> >
> > I have multiple databases, which I can access concurrently 
> by using  
> > the
> > 'attach' command, and then referring to each table using
> > 'database.tablename'. It works well.
> >
> > Now I want to create a foreign key constraint where the 
> parent table  
> > is in
> > one database and the child table is in another database. However,  
> > when I use
> > the syntax 'REFERENCES database.tablename' I get a syntax error.
> >
> > Is it possible to do what I want?
> 
> No. It is not.
> 

Ok, thanks.

Is there any chance of it being considered for a future release?

Frank

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] Foreign key on different database - possible?

[Dan Kennedy]

On Oct 20, 2010, at 3:59 PM, Frank Millman wrote:

> Hi all
>
> I am using SQLite 3.7.2 on Fedora 10.
>
> I have multiple databases, which I can access concurrently by using  
> the
> 'attach' command, and then referring to each table using
> 'database.tablename'. It works well.
>
> Now I want to create a foreign key constraint where the parent table  
> is in
> one database and the child table is in another database. However,  
> when I use
> the syntax 'REFERENCES database.tablename' I get a syntax error.
>
> Is it possible to do what I want?

No. It is not.

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] Foreign key on different database - possible?

[Frank Millman]

Hi all

I am using SQLite 3.7.2 on Fedora 10.

I have multiple databases, which I can access concurrently by using the
'attach' command, and then referring to each table using
'database.tablename'. It works well.

Now I want to create a foreign key constraint where the parent table is in
one database and the child table is in another database. However, when I use
the syntax 'REFERENCES database.tablename' I get a syntax error.

Is it possible to do what I want?

Thanks

Frank Millman

___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users