Composite foreign keys which has common parent foreign key in SQLalchemy

[Kevin Qiu]

In the SQLalchemy documentation, 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',
        }

[Mike Bayer]

On 6/25/15 2:08 PM, Kevin Qiu wrote:

In the SQLalchemy documentation, 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.

[Kevin Qiu]

Yes, the problem was to implement polymorphic relationship. The relationship() was a misunderstanding, I thought I need to specify it still explicitly. The user to staff is one to one relationship, so is the user to student. I changed my design so the email act as primary key instead, and set staff_id, student_no as index. There appears a error when I override the default __init__ method of the User class in order to hash the password.

When I create a new staff object:

admin = Staff(

staff_id = 'admin', 

first_name="admin", 

last_name="admin", 

email="ad...@admin.com", 

password="123456")

It complains  

 File ".\db_create.py", line 14, in <module>

    password="123456")

TypeError: __init__() got an unexpected keyword argument 'staff_id'

class User(mydb.Model):

__tablename__ = 'USER'

__table_args__ = (Index('SearchNameIndices', "last_name", "first_name"), )

email = mydb.Column(mydb.String(62), primary_key=True) 

first_name = mydb.Column(mydb.String(64))

last_name = mydb.Column(mydb.String(64))

password = mydb.Column(mydb.String(), nullable = False)

user_type = mydb.Column(mydb.String)

__mapper_args__ = {

'polymorphic_identity':'user',

'polymorphic_on':user_type

}

def __init__(

self, 

email,

first_name, 

last_name, 

birthdate, 

password

):

self.email = email

self.first_name = first_name

self.last_name = last_name

self.birthdate = birthdate

self.password = bcrypt.generate_password_hash(password)

def is_authenticated(self):

return True

def is_active(self):

return True

def is_anonymous(self):

return False

def get_id(self):

return unicode(self.uid)

class Student(User):

__tablename__ = 'STUDENT'

email = mydb.Column(mydb.String(62), mydb.ForeignKey('USER.email'), primary_key=True) 

study_no = mydb.Column(mydb.String(20), index = True) 

birthdate = mydb.Column(mydb.Date(), index=True)

__mapper_args__ = {

'polymorphic_identity':'STUDENT',

}

class Staff(User):

__tablename__ = 'STAFF'

email = mydb.Column(mydb.String(62), mydb.ForeignKey('USER.email'), primary_key=True) 

staff_id = mydb.Column(mydb.String(20), index = True)

__mapper_args__ = {

'polymorphic_identity':'STAFF',

}      

--
You received this message because you are subscribed to a topic in the Google Groups "sqlalchemy" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/sqlalchemy/EZrrKXdS-p0/unsubscribe.
To unsubscribe from this group and all its topics, send an email to sqlalchemy+...@googlegroups.com.
To post to this group, send email to sqlal...@googlegroups.com.
Visit this group at http://groups.google.com/group/sqlalchemy.
For more options, visit https://groups.google.com/d/optout.