Just like you needed to use an ALTER statement to add the keys from
Users->Objects, SQLA needs the same courtesy so it knows that no
dependency should be stated when it inserts a row into all three
tables for your User object. The INSERT of the user knows it must
insert a row into three tables, so it wants to insert in their order
of dependency. This is done for the full set of rows to be inserted
for the "Object" hierarchy so it doesn't necessarily know that the
OwnerUserID column is NULL in all cases. The list of tables needs a
clue as to what their natural dependency order is since its ambiguous
here (you could make the argument that their dependency order *is*
unambiguous due to the mapping applied to them, so perhaps this would
make a nice improvement at some point. in general this is a really
rare occurence).
So autoload "Object" as follows:
object_table = Table('Object', metadata,
Column("OwnerUserID", Integer, ForeignKey("User.UserID",
name='x', use_alter=True)),
Column("LastModifiedUserID", Integer, ForeignKey("User.UserID",
name='x', use_alter=True)),
autoload = True, autoload_with=engine)
the datatypes and foreign key names are not crucial here since you
aren't issuing a CREATE TABLE.
On Jun 4, 2008, at 10:54 AM, Marin wrote:
>
> I have a PostgreSQL database with 3 tables: Object, Role and User:
>
> CREATE TABLE "Object"
> (
> "ObjectID" BIGINT NOT NULL DEFAULT NEXTVAL('"SEQ_ObjectID"'),
> "ObjectTypeID" INT NOT NULL,
> "OwnerUserID" BIGINT NOT NULL,
> "CreatedTimestamp" TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT
> NOW(),
> "LastModifiedUserID" BIGINT DEFAULT NULL,
> CONSTRAINT "PK_Object" PRIMARY KEY ("ObjectID"),
> CONSTRAINT "FK_Object_ObjectType" FOREIGN KEY ("ObjectTypeID")
> REFERENCES "ObjectType"("ObjectTypeID")
> );
>
> CREATE TABLE "Role"
> (
> "RoleID" BIGINT NOT NULL DEFAULT NEXTVAL('"SEQ_ObjectID"'),
> CONSTRAINT "PK_Role" PRIMARY KEY ("RoleID"),
> CONSTRAINT "FK_Role_Object" FOREIGN KEY ("RoleID") REFERENCES
> "Object"("ObjectID")
> ON DELETE CASCADE
> );
>
> CREATE TABLE "User"
> (
> "UserID" BIGINT NOT NULL DEFAULT NEXTVAL('"SEQ_ObjectID"'),
> CONSTRAINT "PK_User" PRIMARY KEY ("UserID"),
> CONSTRAINT "FK_User_Role" FOREIGN KEY ("UserID") REFERENCES
> "Role"("RoleID")
> ON DELETE RESTRICT
> );
>
> ALTER TABLE "Object"
> ADD CONSTRAINT "FK_Object_owner" FOREIGN KEY ("OwnerUserID")
> REFERENCES "User"("UserID")
> ON DELETE RESTRICT;
> ALTER TABLE "Object"
> ADD CONSTRAINT "FK_Object_last_modified" FOREIGN KEY
> ("LastModifiedUserID") REFERENCES "User"("UserID")
> ON DELETE RESTRICT;
>
> Then I reflect the tables:
> object_table = sa.Table('Object', SA_Metadata, autoload = True,
> autoload_with=engine)
> role_table = sa.Table('Role', SA_Metadata, autoload = True,
> autoload_with=engine)
> user_table = sa.Table('User', SA_Metadata, autoload = True,
> autoload_with=engine)
>
> And map them:
> orm.mapper(db.Object, object_table,
> polymorphic_on=object_table.c.ObjectTypeID, polymorphic_identity=1)
> orm.mapper(db.Role, role_table, inherits=db.Object,
> polymorphic_identity=2)
> orm.mapper(db.User, user_table, inherits=db.Role,
> polymorphic_identity=3)
>
> And when I try to insert a new User:
> user = db.User()
> user.OwnerUserID = 1
> db_session.save(user)
> db_session.flush()
>
> I get this error:
> sqlalchemy.exceptions.CircularDependencyError: Circular dependency
> detected [(Role, User), (Object, Role), (User, Object)][]
>
> The tables are circular, but the UserID and OwnerUserID are never
> identical for any User that SQLAlchemy should insert. I spent this
> whole day looking for a solution, but there were none.
>
> >
--~--~---------~--~----~------------~-------~--~----~
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To post to this group, send email to sqlalchemy@googlegroups.com
To unsubscribe from this group, send email to [EMAIL PROTECTED]
For more options, visit this group at
http://groups.google.com/group/sqlalchemy?hl=en
-~----------~----~----~----~------~----~------~--~---
