[Tutor] cant autoincrement

eryksun eryksun at gmail.com
Sat Apr 27 00:18:13 CEST 2013


On Fri, Apr 26, 2013 at 8:45 AM, Lolo Lolo <losermeloser at yahoo.com> wrote:
>
> id INTERGER Primary Key
>
> the database has a few more fields, but when i insert into it, i leave the
> id field empty as it should be done automatically, but sql complains with an
> error that i have left the field blank. If i manually put the ids in,
> everything works, but from my understanding this should be done
> automatically, aswell as it auto incrementing?

The id should be NULL.  For parameter substitution use None.

> i have two tables
> table1 (id, field 2, field 3, field 4)
> table2 (id, field 2, field 3, FOREIGN KEY (field 3) REFERENCES table1(id))

> keys, and strangely enough if i put random text into the foreign key of
> table 2 that didnt reference any ids on table1, sqlite still inserts them
> with no errors when they clearly ignore the instructions given.

The foreign keys constrain is initially disabled. There's a pragma to
enable it. Here's an example:

    import sqlite3
    con = sqlite3.connect(':memory:')
    cur = con.cursor()

    # enable foreign keys constraint
    cur = cur.execute('pragma foreign_keys=on')

    cur.execute('''
    create table table1(
      id integer primary key, field2, field3, field4)''')

    cur.execute('''
    create table table2(
      id integer primary key, field2, field3,
      foreign key(field3) references table1(id))''')

    cur.execute('insert into table1 values(?,?,?,?)', (None, 1, 2, 3))
    cur.execute('insert into table2 values(?,?,?)', (None, 4, 1))

    # this should raise
    # sqlite3.IntegrityError: foreign key constraint failed
    cur.execute('insert into table2 values(?,?,?)', (None, 5, 2))


More information about the Tutor mailing list