Problem trying to re-use a primary key id with SQLAlchemy

I'm trying to reuse a primary key in one of my tables with SQLAlch开发者_运维技巧emy and am getting foreign key constraint error.

In a nutshell:

• PostgreSQL 8.4
• Python 2.7
• SQLAlchemy 0.7

I have 3 tables: User, Inventories and Devices. Inventories and Devices have a one-to-one relationship with User. User.id is Inventories.user_id and Devices.user_id foreign keyed.

I've got User, Devices and Inventories set up in models/ according to standard python practices.

Within interactive python I can issue the following commands no problem:

>>>newUser = User.create()
>>>newUser.device = User.create_device(<*args>)
>>>Session.add(newUser)
>>>Session.commit()

(an inventory record is automatically created in code)

Now, let's say I want to re-use User record 1 (it's the only record that will allow a method called reset in code for security and internal testing reasons)

>>>oldUser = User.retrieve(1)
>>>Session.delete(oldUser)
>>>Session.commit()

(confirm that user 1 no longer exists)

>>>newUser = User.create()
>>>newUser.device = User.create_device(<*args>)
>>>newUser.id = 1
>>>Session.add(newUser)
>>>Session.commit()

At this point I'll either get an eror that Key(id)=(<id>) is still referenced from table "devices" (or "inventories") where <id> is the newUser.id before re-assigning it to be id 1

I've looked into cascading and have tried the various options (all, save-update, etc) with no effect.

Any information pointing to where I'm going wrong would greatly be appreciated,

Thanks,

Krys

To address the error you're seeing, you could update the foreign keys on all of the Device and Inventory models associated with that User model before committing. You'll have to make sure that your User model doesn't auto-increment the id (i.e., that it isn't a PostgreSQL sequence).

For example, the SQLAlchemy model declaration should be

class User(base):
    __tablename__ = 'user'
    id = Column('id', Integer, primary_key=True, unique=True, nullable=False)

instead of

class User(base):
    __tablename__ = 'user'
    id = Column('id', Integer, Sequence('user_id_seq'), primary_key=True)

BUT, this is probably not the right way to do it! It would be a better design to use a sequence on User.id (like in the second model declaration), and add another field on the user table that indicates if the user is an admin (for the security/testing purposes you mentioned). This way you don't have to rely on magic numbers in your application (e.g., the user id) for application logic, especially security.

I ma not using SQLAlchemy, so i do not have a proper answer, but i can say that you must ask yourself what you want is really necessary?

Because,

• You probably will break the data integrity, and that may couse serious problems.
• You will need to break the auto-increment structure of the ID, so until then, you have to assign id's by hand or use a hand-written pre-save trigger to get a proper id.
• If you have tables that have a User foreginkey that sets NOT null, thn you probably will have problem with freeing records related to a deleted user. If you do not null them, a re-used id will create a serious data-integrity problem (wrongly referanced relations)...

So first of all, you must decide if it worth it?

Since this is a problem that shouldn't be seen in production, just use SET CONSTRAINTS. You could use INITIALLY DEFERRED on your FOREIGN KEYs but I wouldn't recommend that since you're not dealing with a cyclic dependency that exists in production.