title: flask sqlalchemy.exc.NoForeignKeysError NoForeignKeysError: Could not determine join condition
body: <p>Here is the exception trace in the debugger</p>
  
  <pre><code>sqlalchemy.exc.NoForeignKeysError
  
  NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Department.employees - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
  </code></pre>
  
  <p>This is my models.py, I'm using reflection with an existing schema.</p>
  
  <pre><code>    from sqlalchemy import Table, Column, Integer, ForeignKey, String, DateTime, Date, Time
      from sqlalchemy.orm import relationship, backref
  
      #make sure we get the right base
      from database import ProbeDB_Base, DB
  
      class Location(ProbeDB_Base):
          __tablename__ = u'Location'
          __table_args__ = {u'schema': u'probedb'}
  
          LocationID = Column(Integer, primary_key=True)
          LocationName = Column(String(100))
          controllers = relationship("Controller", backref="Location")
  
      class Controller(ProbeDB_Base):
          __tablename__ = u'Controller'
          __table_args__ = {u'schema': 'probedb'}
  
          ControllerID = Column(Integer, primary_key=True)
          ControllerName = Column(String(100))
          ControllerNo = Column(Integer)
          SlaveNo = Column(Integer)
          IPAddress = Column(String(20))
          Port = Column(Integer)
          CommunicationType = Column(Integer)
          Enable = Column(Integer)
          ResponseTime = Column(Integer)
          LocationID = Column(Integer, ForeignKey('Location.LocationID'), index=True)
          #LocationID = relationship(Location, primaryjoin='Controller.LocationID == DB.Location.LocationID')
          MaxChannels = Column(Integer)
          #Location_id = relationship(u'Location')
          transactions = relationship("Transaction", backref="Controller")
  
      class Department(ProbeDB_Base):
          __tablename__ = u'Department'
          __table_args__ = {u'schema': 'probedb'}
  
          DepartmentID = Column(Integer, primary_key=True)
          Name = Column(String(100))
          employees = relationship("Employee", backref="Department")
  
      class Designation(ProbeDB_Base):
          __tablename__ = u'Designation'
          __table_args__ = {u'schema': 'probedb'}
  
          DesignationID = Column(Integer, primary_key=True)
          Name = Column(String(100))
          employees = relationship("Employee", backref="Designation")
  
      class Employee(ProbeDB_Base):
          __tablename__ = u'Employee'
          __table_args__ = {u'schema': 'probedb'}
  
          EmployeeID = Column(Integer, primary_key=True)
          EmployeeCode = Column(String(15), index=True)
          LocationID = Column(Integer)
          Name = Column(String(50))
          CardNo = Column(Integer)
          DepartmentID = Column(Integer, ForeignKey('Department.DepartmentID'), index=True)
          #DepartmentID = relationship(Department, primaryjoin='Employee.DepartmentID == DB.Department.DepartmentID')
          DesignationID = Column(Integer, ForeignKey('Designation.DesignationID'), index=True)
          #DesignationID = relationship(Designation, primaryjoin='Employee.DesignationID == DB.Designation.DesignationID')
          EnrollDate = Column(DateTime)
          ExpDate = Column(DateTime)
          #Department_id = relationship(u'Department')
          #Designation_id = relationship(u'Designation')
  
      class Event(ProbeDB_Base):
          __tablename__ = u'Event'
          __table_args__ = {u'schema': 'probedb'}
  
          EventID = Column(Integer, primary_key=True)
          Name = Column(String(100))
  
          transactions = relationship("Transaction", backref="Event")
  
      class Transaction(ProbeDB_Base):
          __tablename__ = u'Transaction'
          __table_args__ = {u'schema': 'probedb'}
  
          TransactionID = Column(Integer, primary_key=True)
          DateTime = Column(DateTime)
          Date = Column(Date)
          Time = Column(Time)
          CardNo = Column(Integer)
          Channel = Column(Integer)
          EventID = Column(Integer, ForeignKey('Event.EventID'), index=True)
          #EventID = relationship(Event, primaryjoin='Transaction.EventID == DB.Event.EventID')
          AccessType = Column(String(4))
          ReadPtr = Column(Integer)
          ControllerID = Column(Integer, ForeignKey('Controller.ControllerID'), index=True)
          #ControllerID = relationship("Controller", primaryjoin='Transaction.ControllerID == Controller.ControllerID')
          IPAddress = Column(String(20))
          #Controller_id = relationship(u'Controller')
          #Event_id = relationship(u'Event')
  
      class User(ProbeDB_Base):
          __tablename__ = u'users'
          __table_args__ = {u'schema': 'probedb'}
  
          uid = Column(Integer, primary_key=True)
          firstname = Column(String(100), nullable=False)
          lastname = Column(String(100), nullable=False)
          email = Column(String(120), nullable=False, index=True)
          pwdhash = Column(String(100), nullable=False)
  </code></pre>
  
  <p>I did specify a foreign key for the transaction table and a backref in the controller table, but not able to figure out where I am going wrong</p>
  
  <p>Here is the <code>create.sql</code> for the table</p>
  
  <pre><code>    /*
      ProbeDB 
      This is the setup SQL for probedb
      */
  
      CREATE TABLE IF NOT EXISTS Location(
      LocationID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      LocationName varchar(100)
      )
      ;
      CREATE TABLE IF NOT EXISTS Controller(
      ControllerID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      ControllerName varchar(100),
      ControllerNo int,
      SlaveNo int,
      CONSTRAINT SlaveNo_Ck CHECK (SlaveNo BETWEEN 1 AND 128),
      IPAddress varchar(20),
      Port int,
      CommunicationType int,
      CONSTRAINT CommunicationType_Ck CHECK (CommunicationType BETWEEN 1 AND 2),
      /* 
      1 TCP
      2 UDP
      */
      Enable int, 
      CONSTRAINT Enable_Ck CHECK (Enable BETWEEN 0 AND 1),
      /* 
      0 Enable
      1 Disable
      */
      ResponseTime int,
      LocationID int,
      CONSTRAINT fk_LocationReference FOREIGN KEY (LocationID) REFERENCES Location(LocationID),
      MaxChannels int
      )
      ;
      CREATE TABLE IF NOT EXISTS Department(
      DepartmentID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      Name varchar(100)
      )
      ;
      CREATE TABLE IF NOT EXISTS Designation(
      DesignationID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      Name varchar(100)
      )
      ;
      CREATE TABLE IF NOT EXISTS Employee(
      EmployeeID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      EmployeeCode varchar(15) UNIQUE,
      LocationID int,
      Name varchar(50),
      CardNo int,
      DepartmentID int,
      CONSTRAINT fk_DepartmentReference FOREIGN KEY (DepartmentID) REFERENCES Department(DepartmentID),
      DesignationID int,
      CONSTRAINT fk_DesignationReference FOREIGN KEY (DesignationID) REFERENCES Designation(DesignationID),
      EnrollDate datetime,
      ExpDate datetime
      )
      ;
      CREATE TABLE IF NOT EXISTS Event(
      EventID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      Name varchar(100)
      )
      ;
      CREATE TABLE IF NOT EXISTS Transaction(
      TransactionID int NOT NULL AUTO_INCREMENT PRIMARY KEY,
      DateTime datetime,
      Date date,
      Time time,
      CardNo int,
      Channel int,
      EventID int,
      CONSTRAINT fk_EventReference FOREIGN KEY (EventID) REFERENCES Event(EventID),
      AccessType varchar(4),
      ReadPtr int,
      ControllerID int,
      CONSTRAINT fk_ControllerReference FOREIGN KEY (ControllerID) REFERENCES Controller(ControllerID),
      IPAddress varchar(20)
      )
      ;
      CREATE TABLE users (
      uid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
      firstname VARCHAR(100) NOT NULL,
      lastname VARCHAR(100) NOT NULL,
      email VARCHAR(120) NOT NULL UNIQUE,
      pwdhash VARCHAR(100) NOT NULL
      );
      /*
      END
      */
  </code></pre>
  
  <p>What am I doing wrong?</p>
  
  <p>EDITED After modifying per Miguel</p>
  
  <pre><code>    from sqlalchemy import Table, Column, Integer, ForeignKey, String, DateTime, Date, Time
      from sqlalchemy.orm import relationship, backref
  
      #make sure we get the right base
      from database import ProbeDB_Base, DB
  
      class Location(ProbeDB_Base):
          __tablename__ = u'Location'
          __table_args__ = {u'schema': u'probedb'}
  
          LocationID = Column(Integer, primary_key=True)
          LocationName = Column(String(100))
          controllers = relationship("Controller", backref="Location")
  
      class Controller(ProbeDB_Base):
          __tablename__ = u'Controller'
          __table_args__ = {u'schema': 'probedb'}
  
          ControllerID = Column(Integer, primary_key=True)
          ControllerName = Column(String(100))
          ControllerNo = Column(Integer)
          SlaveNo = Column(Integer)
          IPAddress = Column(String(20))
          Port = Column(Integer)
          CommunicationType = Column(Integer)
          Enable = Column(Integer)
          ResponseTime = Column(Integer)
          LocationID = Column(Integer, ForeignKey('Location'), index=True)
          #LocationID = relationship(Location, primaryjoin='Controller.LocationID == DB.Location.LocationID')
          MaxChannels = Column(Integer)
          #Location_id = relationship(u'Location')
          transactions = relationship("Transaction", backref="Controller")
  
      class Department(ProbeDB_Base):
          __tablename__ = u'Department'
          __table_args__ = {u'schema': 'probedb'}
  
          DepartmentID = Column(Integer, primary_key=True)
          Name = Column(String(100))
          employees = relationship("Employee", backref="Department")
  
      class Designation(ProbeDB_Base):
          __tablename__ = u'Designation'
          __table_args__ = {u'schema': 'probedb'}
  
          DesignationID = Column(Integer, primary_key=True)
          Name = Column(String(100))
          employees = relationship("Employee", backref="Designation")
  
      class Employee(ProbeDB_Base):
          __tablename__ = u'Employee'
          __table_args__ = {u'schema': 'probedb'}
  
          EmployeeID = Column(Integer, primary_key=True)
          EmployeeCode = Column(String(15), index=True)
          LocationID = Column(Integer)
          Name = Column(String(50))
          CardNo = Column(Integer)
          DepartmentID = Column(Integer, ForeignKey('Department'), index=True)
          #DepartmentID = relationship(Department, primaryjoin='Employee.DepartmentID == DB.Department.DepartmentID')
          DesignationID = Column(Integer, ForeignKey('Designation'), index=True)
          #DesignationID = relationship(Designation, primaryjoin='Employee.DesignationID == DB.Designation.DesignationID')
          EnrollDate = Column(DateTime)
          ExpDate = Column(DateTime)
          #Department_id = relationship(u'Department')
          #Designation_id = relationship(u'Designation')
  
      class Event(ProbeDB_Base):
          __tablename__ = u'Event'
          __table_args__ = {u'schema': 'probedb'}
  
          EventID = Column(Integer, primary_key=True)
          Name = Column(String(100))
  
          transactions = relationship("Transaction", backref="Event")
  
      class Transaction(ProbeDB_Base):
          __tablename__ = u'Transaction'
          __table_args__ = {u'schema': 'probedb'}
  
          TransactionID = Column(Integer, primary_key=True)
          DateTime = Column(DateTime)
          Date = Column(Date)
          Time = Column(Time)
          CardNo = Column(Integer)
          Channel = Column(Integer)
          EventID = Column(Integer, ForeignKey('Event'), index=True)
          #EventID = relationship(Event, primaryjoin='Transaction.EventID == DB.Event.EventID')
          AccessType = Column(String(4))
          ReadPtr = Column(Integer)
          ControllerID = Column(Integer, ForeignKey('Controller'), index=True)
          #ControllerID = relationship("Controller", primaryjoin='Transaction.ControllerID == Controller.ControllerID')
          IPAddress = Column(String(20))
          #Controller_id = relationship(u'Controller')
          #Event_id = relationship(u'Event')
  
      class User(ProbeDB_Base):
          __tablename__ = u'users'
          __table_args__ = {u'schema': 'probedb'}
  
          uid = Column(Integer, primary_key=True)
          firstname = Column(String(100), nullable=False)
          lastname = Column(String(100), nullable=False)
          email = Column(String(120), nullable=False, index=True)
          pwdhash = Column(String(100), nullable=False)
  </code></pre>
  
  <p>I get this exception</p>
  
  <pre><code>sqlalchemy.exc.NoForeignKeysError
  
  NoForeignKeysError: Could not determine join condition between parent/child tables on relationship Designation.employees - there are no foreign keys linking these tables. Ensure that referencing columns are associated with a ForeignKey or ForeignKeyConstraint, or specify a 'primaryjoin' expression.
  </code></pre>