Issue: pwiz not generating foreign key fields?
Author: jfmatth
State: closed
Created: 2013-06-18 19:47:50

First off, love this project, keep it up!

I have a DB with lots of tables, but it's not 'connecting' the FK's.

Here's a sample:

```
describe Tickets
--------------

+-----------------+--------------+------+-----+--------------+----------------+
| Field           | Type         | Null | Key | Default      | Extra          |
+-----------------+--------------+------+-----+--------------+----------------+
| id              | int(11)      | NO   | PRI | NULL         | auto_increment |
| EffectiveId     | int(11)      | NO   | MUL | 0            |                |
| Queue           | int(11)      | NO   | MUL | 0            |                |
| Type            | varchar(16)  | YES  |     | NULL         |                |
| IssueStatement  | int(11)      | NO   |     | 0            |                |
| Resolution      | int(11)      | NO   |     | 0            |                |
| Owner           | int(11)      | NO   | MUL | 0            |                |
| Subject         | varchar(200) | YES  | MUL | [no subject] |                |
| InitialPriority | int(11)      | NO   |     | 0            |                |
| FinalPriority   | int(11)      | NO   |     | 0            |                |
| Priority        | int(11)      | NO   |     | 0            |                |
| TimeEstimated   | int(11)      | NO   |     | 0            |                |
| TimeWorked      | int(11)      | NO   |     | 0            |                |
| Status          | varchar(10)  | YES  |     | NULL         |                |
| TimeLeft        | int(11)      | NO   |     | 0            |                |
| Told            | datetime     | YES  |     | NULL         |                |
| Starts          | datetime     | YES  |     | NULL         |                |
| Started         | datetime     | YES  |     | NULL         |                |
| Due             | datetime     | YES  |     | NULL         |                |
| Resolved        | datetime     | YES  |     | NULL         |                |
| LastUpdatedBy   | int(11)      | NO   |     | 0            |                |
| LastUpdated     | datetime     | YES  |     | NULL         |                |
| Creator         | int(11)      | NO   | MUL | 0            |                |
| Created         | datetime     | YES  |     | NULL         |                |
| Disabled        | smallint(6)  | NO   |     | 0            |                |
+-----------------+--------------+------+-----+--------------+----------------+
25 rows in set (0.00 sec)
```

with pwiz's output:

``` python

class Tickets(BaseModel):
    created = DateTimeField(null=True, db_column='Created')
    creator = IntegerField(db_column='Creator')
    disabled = IntegerField(db_column='Disabled')
    due = DateTimeField(null=True, db_column='Due')
    effectiveid = IntegerField(db_column='EffectiveId')
    finalpriority = IntegerField(db_column='FinalPriority')
    initialpriority = IntegerField(db_column='InitialPriority')
    issuestatement = IntegerField(db_column='IssueStatement')
    lastupdated = DateTimeField(null=True, db_column='LastUpdated')
    lastupdatedby = IntegerField(db_column='LastUpdatedBy')
    owner = IntegerField(db_column='Owner')
    priority = IntegerField(db_column='Priority')
    queue = IntegerField(db_column='Queue')
    resolution = IntegerField(db_column='Resolution')
    resolved = DateTimeField(null=True, db_column='Resolved')
    started = DateTimeField(null=True, db_column='Started')
    starts = DateTimeField(null=True, db_column='Starts')
    status = CharField(null=True, db_column='Status')
    subject = CharField(null=True, db_column='Subject')
    timeestimated = IntegerField(db_column='TimeEstimated')
    timeleft = IntegerField(db_column='TimeLeft')
    timeworked = IntegerField(db_column='TimeWorked')
    told = DateTimeField(null=True, db_column='Told')
    type = CharField(null=True, db_column='Type')

    class Meta:
        db_table = 'Tickets'
```

Any idea how i can get them to match up so I don't have to do the FK's by hand?

thanks


edit: fixed formatting.


Are you using MySQL?


If you're using MySQL you might test out using this query, which is how pwiz finds foreign keys:

``` sql
SELECT column_name, referenced_table_name, referenced_column_name
FROM information_schema.key_column_usage
WHERE table_name = "your table name here"
  AND table_schema = DATABASE()
  AND referenced_table_name IS NOT NULL
  AND referenced_column_name IS NOT NULL
```


Yes we are.

I'll run the query and show results.  Thanks for the quick feedback

J
---------- Forwarded message ----------
From: "Charles Leifer" notifications@github.com
Date: Jun 21, 2013 7:58 AM
Subject: Re: [peewee] pwiz not generating foreign key fields? (#207)
To: "coleifer/peewee" peewee@noreply.github.com
Cc: "John Matthew" john@compunique.com

Are you using MySQL?

—
Reply to this email directly or view it on
GitHubhttps://github.com/coleifer/peewee/issues/207#issuecomment-19820920
.


My associate at work says it may be the type of DB we are running, MyISAM, which doesn't understand true FK's.

See below:

```
mysql> SELECT column_name, referenced_table_name, referenced_column_name FROM information_schema.key_column_usage WHERE table_name = "Tickets"   AND table_schema = DATABASE()   AND referenced_table_name IS NOT NULL   AND referenced_column_name IS NOT NULL;
Empty set (0.00 sec)

mysql> show create table Tickets ;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table   | Create Table                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                     |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Tickets | CREATE TABLE `Tickets` (
  `id` int(11) NOT NULL auto_increment,
  `EffectiveId` int(11) NOT NULL default '0',
  `Queue` int(11) NOT NULL default '0',
  `Type` varchar(16) default NULL,
  `IssueStatement` int(11) NOT NULL default '0',
  `Resolution` int(11) NOT NULL default '0',
  `Owner` int(11) NOT NULL default '0',
  `Subject` varchar(200) default '[no subject]',
  `InitialPriority` int(11) NOT NULL default '0',
  `FinalPriority` int(11) NOT NULL default '0',
  `Priority` int(11) NOT NULL default '0',
  `TimeEstimated` int(11) NOT NULL default '0',
  `TimeWorked` int(11) NOT NULL default '0',
  `Status` varchar(10) default NULL,
  `TimeLeft` int(11) NOT NULL default '0',
  `Told` datetime default NULL,
  `Starts` datetime default NULL,
  `Started` datetime default NULL,
  `Due` datetime default NULL,
  `Resolved` datetime default NULL,
  `LastUpdatedBy` int(11) NOT NULL default '0',
  `LastUpdated` datetime default NULL,
  `Creator` int(11) NOT NULL default '0',
  `Created` datetime default NULL,
  `Disabled` smallint(6) NOT NULL default '0',
  PRIMARY KEY  (`id`),
  KEY `Tickets1` (`Queue`,`Status`),
  KEY `Tickets2` (`Owner`),
  KEY `Tickets6` (`EffectiveId`,`Type`),
  KEY `Creator` (`Creator`),
  KEY `Subject` (`Subject`)
) ENGINE=MyISAM AUTO_INCREMENT=73183 DEFAULT CHARSET=latin1 |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql>
```

I hope this helps.  

J


Unfortunately, pwiz does not understand how to work with MyISAM.  Currently I have no plans for adding this support.


Charles, thank you for taking the time to look at this.  I figured that would be the case.

Keep up the excellent work on peewee, it's a great "little" product.

J