On 6/25/15 2:08 PM, Kevin Qiu wrote:
In the SQLalchemy documentation
<http://docs.sqlalchemy.org/en/latest/orm/inheritance.html#basic-control-of-which-tables-are-queried>,
it states
"It is standard practice that the same column is used for both the
role of primary key as well as foreign key to the parent table,
and that the column is also named the same as that of the parent
table. However, both of these practices are optional. Separate
columns may be used for primary key and parent-relationship, the
column may be named differently than that of the parent, and even
a custom join condition can be specified between parent and child
tables instead of using a foreign key."
So I here have a parent table: user, child table: staff and student.
Staff has staff id, student has student id which follow different
format so they can't be mixed. I tried two ways to solve the problem.
wait, what is "the problem" ? what is the relationship of these three
tables intended to be? is a staff/student one-to-one with a user? do
you want to use class inheritance here? I see you are using
relationship() below, so maybe not.
Approach 1: I could introduce a surrogate key for user table, name it
uid. And it's used as foreign key in the child table. But then I
introduce a composite primary key since student id and staff id was
designed to be primary key already. And in one of the dependent table,
it have foreign keys to both student table and staff table, which
refers to the same uid. This where the problem comes.
|And I receive error: sqlalchemy.exc.ArgumentError:
ForeignKeyConstraint on PROJECT_APP(study_no, staff_id, uid) refers to
multiple remote tables: STAFF and STUDENT|
then also what is PROJECT_APP in relation to these? I don't understand
the intent of that constraint.||
|Approach 2: I use children's primary keys as foreign keys in the user
table and discard inherit parent's primary key: |
If this were inheritance, then you'd follow the form in the docs; the
User class has no ForeignKey constraints on it. Each of Student and
Staff contain an individual foreign key constraint referring to User.
You would follow the form at
http://docs.sqlalchemy.org/en/rel_1_0/orm/inheritance.html#joined-table-inheritance.
--
You received this message because you are subscribed to the Google Groups
"sqlalchemy" group.
To unsubscribe from this group and stop receiving emails from it, send an email
to [email protected].
To post to this group, send email to [email protected].
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
