SQLite3 in Python 2.7 Rejecting Foreign Key Insert

[Chris Angelico]

On Tue, Nov 25, 2014 at 5:44 AM, Dennis Lee Bieber
<wlfraed at ix.netcom.com> wrote:
> On Mon, 24 Nov 2014 10:05:11 +0200, "Frank Millman" <frank at chagford.com>
> declaimed the following:
>
>>I will explain why such a feature would have been useful for me.
>>
>>My accounting system handles multiple companies. I needed to figure out a
>>way to keep each one's data separate from the others.
>>
>>Originally I supported two databases - PostgreSQL and Sql Server. They both
>>have a concept called 'schemas', which handles my requirement elegantly -
>>each company's data is stored in its own schema.
>>
>
>         We must have a different impression of what a "schema" consists of. As
> I learned it, the "schema" basically came down to the statements defining
> the form of the data, the rules (triggers) on it, and the relationships
> between data items. http://en.wikipedia.org/wiki/Database_schema
>
>         By that definition, SQLite3 does support "schemas" (it's all the DDL
> statements used to create the database less the data itself -- which are
> DML). So does Firebird, MySQL, Access/JET

The sense here is one of qualified names. In DB2 and PostgreSQL, you
can create a database, and then within that, create schemas. You
choose a database when you connect, and then you can write code like
this:

select customer_name, invoice_date from accts.invoices join
crm.customers on (customer_id = crm.customers.id) where blah blah

The table names are qualified with their schema names. You can also
have a "default schema", which is the one used for unqualified table
names, which lets you pick one from a group of related data sets: for
example, Frank's accounting system would probably make use of that,
such that the code needn't care after startup. It'll just select a
default schema (or, in PostgreSQL, alter the search_path), and then
most of the code will just use unqualified names. Any data that's not
company-specific (maybe the application stores UI config data in the
database too) can be stored in a dedicated schema and referenced by
qualified name.

Technically, a table can be referred to as
database_name.schema_name.table_name (and then you can add
.column_name after that, but I have NEVER seen anyone use the full
four-parter!), but in PostgreSQL, the database_name, if present, must
be the one that you connected to. In MySQL, there these things that
are kinda databases and kinda schemas, are referred to by both names,
and in terms of data separation, primarily act like schemas. In
SQLite3, there are basically no schemas, AIUI, but you can use
databases to do something similar - with the originally-mentioned
limitation.

ChrisA