custom table creation by install script error

Question

I am using magento 1.8. I am creating my custom table through magento model install script,

<?php
//die('jfjf n');
$installer = $this;
$installer->startSetup();
$installer->run("DROP TABLE IF EXISTS {$installer->getTable('commission')};
CREATE TABLE {$installer->getTable('commission')} (
  `id` int(11) unsigned NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `product_type` int(11) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `foundation_id` int(11) NOT NULL,
  `mano_admin_fees` decimal(10,2) NOT NULL,
  `mano_promotion_fees` decimal(10,2) NOT NULL,
  `paypal_fees` decimal(10,2) NOT NULL,
  `foundation_fees` decimal(10,2),
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
$installer->endSetup();

I am getting following error,

SQLSTATE[42000]: Syntax error or access violation: 1075 Incorrect table definition; there can be only one auto column and it must be defined as a key

I am googled for this lot and made changes to it but not working, I also checked magento database table core_resource their is no table called commission my config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Npm_Commission>
            <version>0.1.0</version>
        </Npm_Commission>
    </modules>
    <global>
        <resources>
            <commission_setup>
                <setup>
                    <module>Npm_Commission</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </commission_setup>
            <commission_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </commission_write>
            <commission_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </commission_read>
        </resources>
        <models>
            <commission>
                <class>Npm_Commission_Model</class>
                <resourceModel>commission_mysql4</resourceModel>
            </commission>
            <commission_mysql4>
                <class>Npm_Commission_Model_Mysql4</class>
                <entities>
                    <commission>
                        <table>commission</table>
                    </commission>
                </entities>
            </commission_mysql4>
        </models>
    </global>
</config>

Any wrong with my code? Thanks in advance.:)

Answer 1

Thanks guys, I got answer. I dont know what I made mistake in code. The above code gave me error, My New code is

<?php
$installer = $this;
$installer->startSetup();
$installer->run("DROP TABLE IF EXISTS {$installer->getTable('commission')};
CREATE TABLE {$installer->getTable('commission')} (
  `id` int(11) unsigned NOT NULL auto_increment,
  `product_id` int(11) NOT NULL,
  `product_type` int(11) NOT NULL,
  `transaction_id` int(11) NOT NULL,
  `store_id` int(11) NOT NULL,
  `foundation_id` int(11) NOT NULL,
  `mano_admin_fees` decimal(10,2) NOT NULL,
  `mano_promotion_fees` decimal(10,2) NOT NULL,
  `paypal_fees` decimal(10,2) NOT NULL,
  `foundation_fees` decimal(10,2),
  PRIMARY KEY (`id`)
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
");
$installer->endSetup();

config.xml

<?xml version="1.0"?>
<config>
    <modules>
        <Npm_Commission>
            <version>0.1.0</version>
        </Npm_Commission>
    </modules>
    <global>
        <resources>
            <commission_setup>
                <setup>
                    <module>Npm_Commission</module>
                </setup>
                <connection>
                    <use>core_setup</use>
                </connection>
            </commission_setup>
            <commission_write>
                <connection>
                    <use>core_write</use>
                </connection>
            </commission_write>
            <commission_read>
                <connection>
                    <use>core_read</use>
                </connection>
            </commission_read>
        </resources>
        <models>
            <commission>
                <class>Npm_Commission_Model</class>
                <resourceModel>commission_mysql4</resourceModel>
            </commission>
            <commission_mysql4>
                <class>Npm_Commission_Model_Mysql4</class>
                <entities>
                    <commission>
                        <table>commission</table>
                    </commission>
                </entities>
            </commission_mysql4>
        </models>
    </global>
</config>

Thanks for your useful comments guys:)