SQLAlchemy does not create Foreign keys on MySQL [Debian Linux]

Question

For same code snippet, SQLAlchemy creates Foreign Key constraint on Mac OX, however, it fails to create foreign key constraint on Debian Linux.

Environment & Libraries:

Python 2.7

SQLAlchemy 0.7.8

MySQL-python-1.2.3 [This is the connector, and I am suspecting something is wrong here]

Code Snippet:

class PRStatusCV(globalBase):

    __tablename__ = 'pr_status_cv'
    pr_status_cv_id = Column(Integer, primary_key=True)
    name            = Column(VARCHAR(100), nullable=False, unique=True)

    def __init__(self, id, name, desc):
        self.pr_status_cv_id = id
        self.name            = name
        self.description     = desc



class PhysicalRun(globalBase):

    __tablename__ = 'prs'
    pr_id       = Column(Integer, primary_key=True)
    run_name              = Column(VARCHAR(200), nullable=False, unique=True)
    pr_status_cv_id       = Column(Integer, ForeignKey('pr_status_cv.pr_status_cv_id'))

    def __init__(self, name, status):
        self.run_name              = name
        self.pr_status_cv_id       = status

In Debian Linux, the table created contains:

CREATE TABLE `prs` (
  `pr_id` int(11) NOT NULL AUTO_INCREMENT,
  `run_name` varchar(200) NOT NULL,
  `pr_status_cv_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`pr_id`),
  UNIQUE KEY `run_name` (`run_name`),
  KEY `pr_status_cv_id` (`pr_status_cv_id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 |

In Mac OSX the table created looks like this:

CREATE TABLE `prs` (
  `pr_id` int(11) NOT NULL AUTO_INCREMENT,
  `run_name` varchar(200) NOT NULL,
  `pr_status_cv_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`pr_id`),
  UNIQUE KEY `run_name` (`run_name`),
  KEY `pr_status_cv_id` (`pr_status_cv_id`),
  CONSTRAINT `prs_ibfk_1` FOREIGN KEY (`pr_status_cv_id`) REFERENCES `pr_status_cv` (`pr_status_cv_id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Observation: Seems like for Mac OSX SQLAlchemy chose InnoDB DB Engine by default, and MyISAM is chosen for Debian. Any idea why this is happening so?

What modifications do I need to make to have constraint - CONSTRAINT `prs_ibfk_1` FOREIGN KEY (`pr_status_cv_id`) REFERENCES `pr_status_cv`, instead of just KEY `pr_status_cv_id` (`pr_status_cv_id`)?

Is anything incorrect with my Debian Python deploy?

MyIsam don't have constrains, so you need to fix the engine problem so solve the key problem — Puggan Se, Jul 16 '12 at 20:52

Puggan Se · Accepted Answer · 2012-07-16T20:54:19.190

1

ENGINE=MyISAM MyISAM dosn't have Foreign Keys

add __table_args__ = {'mysql_engine':'InnoDB'} to get InnoDB insted

edited Jul 16 '12 at 20:54

answered Jul 16 '12 at 20:48

Puggan Se

5,738
2
22
48

Thanks Puggan. I added __table_args__={'mysql_engine':'InnoDB'} and it seems to create the tables with the constraint. – Aijazs Jul 16 '12 at 20:57

score 0 · Answer 2 · answered Jul 16 '12 at 21:00

The default-storage-enginge is most likely set differently across the two systems. Also have a look at option-files for how to configure this option. Ideally, you should not be leaving this to the default; use the mysql_engine option in Table() to force it on mysql connections.

SQLAlchemy does not create Foreign keys on MySQL [Debian Linux]

2 Answers2