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.
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
Code:
class User(mydb.Model):
__tablename__ = 'USER'
uid = mydb.Column(mydb.Integer, primary_key=True)
...
student = mydb.relationship('Student', uselist=False,
backref='user')
staff = mydb.relationship('Staff', uselist=False, backref='user'))
type = mydb.Column(mydb.String)
__mapper_args__ = {
'polymorphic_identity':'user',
'polymorphic_on':type
}
class Student(User):
__tablename__ = 'STUDENT'
uid = mydb.Column(mydb.Integer, mydb.ForeignKey('USER.uid'),
primary_key=True)
study_no = mydb.Column(mydb.String(20), primary_key = True)
...
__mapper_args__ = {
'polymorphic_identity':'student',
}
class Staff(User):
__tablename__ = 'STAFF'
uid = mydb.Column(mydb.Integer, mydb.ForeignKey('USER.uid'),
primary_key=True)
staff_id = mydb.Column(mydb.String(20), primary_key = True)
...
__mapper_args__ = {
'polymorphic_identity':'staff',
}
class ProjectApp(mydb.Model):
__tablename__ = 'PROJECT_APP'
app_id = mydb.Column(mydb.Integer, primary_key = True)
uid = mydb.Column(mydb.Integer)
study_no = mydb.Column(mydb.String(20))
staff_id = mydb.Column(mydb.String(20))
__table_args__ = (
mydb.ForeignKeyConstraint(
['study_no','staff_id', 'uid'],
['STUDENT.study_no', 'STAFF.staff_id','USER.uid']
),
)
Approach 2: I use children's primary keys as foreign keys in the user table and
discard inherit parent's primary key:
This gives the following error:
SAWarning:
Implicitly combining column USER.study_no with column STUDENT.study_no
under attribute 'study_no'. Please configure one or more attributes for these
same-named columns explicitly.
prop = self._property_from_column(key, prop)
SAWarning: Implicitly combining column USER.staff_id with column
STAFF.staff_id under attribute 'staff_id'. Please configure one or more
attributes for these same-named columns explicitly.
prop = self._property_from_column(key, prop)
Code:
class User(mydb.Model):
__tablename__ = 'USER'
uid = mydb.Column(mydb.Integer, primary_key=True)
...
staff_id = mydb.Column(mydb.String(20),
mydb.ForeignKey('STAFF.staff_id'))
study_no = mydb.Column(mydb.String(20),
mydb.ForeignKey('STUDENT.study_no'))
type = mydb.Column(mydb.String)
__mapper_args__ = {
'polymorphic_identity':'user',
'polymorphic_on':type
}
class Student(User):
__tablename__ = 'STUDENT'
study_no = mydb.Column(mydb.String(20), primary_key = True)
...
user = mydb.relationship('User', uselist=False, backref='student')
__mapper_args__ = {
'polymorphic_identity':'student',
}
class Staff(User):
__tablename__ = 'STAFF'
staff_id = mydb.Column(mydb.String(20), primary_key = True)
...
user = mydb.relationship('User', uselist=False, backref='staff')
__mapper_args__ = {
'polymorphic_identity':'staff',
}
--
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 sqlalchemy+unsubscr...@googlegroups.com.
To post to this group, send email to sqlalchemy@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.
