1.CREATE TABLE `tsd_object` (
`obj_id` varchar(50) NOT NULL,
`mdl_id` varchar(50) NOT NULL,
`obj_code` char(3) default '',
`obj_entity` varchar(50) default NULL,
`obj_tablename` varchar(50) NOT NULL,
`obj_pkey` varchar(50) NOT NULL,
`obj_ridkey` varchar(50) default NULL,
`obj_namekey` varchar(50) default NULL,
`obj_fullnameobj` varchar(50) default NULL,
`obj_fullnamekey` varchar(50) default NULL,
`obj_versionkey` varchar(50) default NULL,
`obj_orderkey` varchar(50) default NULL,
`obj_type` varchar(50) NOT NULL default 'normal',
`obj_pktype` varchar(50) NOT NULL default 'normal',
`obj_base` varchar(50) default NULL,
`obj_audited` tinyint(3) NOT NULL default '0',
`obj_printable` tinyint(3) NOT NULL default '0',
`obj_order` int(11) NOT NULL default '0',
PRIMARY KEY (`obj_id`),
UNIQUE KEY `unique_tablename` (`obj_tablename`),
KEY `mdl_id` (`mdl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.CREATE TABLE `tsd_objpropaccess` (
`id` int(11) NOT NULL auto_increment,
`acs_id` varchar(50) NOT NULL default 'default',
`obj_id` varchar(50) NOT NULL,
`tfa_fieldname` varchar(50) NOT NULL,
`tfa_readable` tinyint(4) NOT NULL default '1',
`tfa_editable` tinyint(4) NOT NULL default '0',
`tfa_order` int(11) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_order` (`tfa_order`),
KEY `obj_id` (`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
然后我进行操作
ALTER TABLE tsd_objpropaccess
ADD CONSTRAINT fk_obj_id FOREIGN KEY (`obj_id`) REFERENCES tsd_objprop (`obj_id`) ON DELETE CASCADE ON UPDATE CASCADE;
能执行,但并没有建立外键,为什么啊?
`obj_id` varchar(50) NOT NULL,
`mdl_id` varchar(50) NOT NULL,
`obj_code` char(3) default '',
`obj_entity` varchar(50) default NULL,
`obj_tablename` varchar(50) NOT NULL,
`obj_pkey` varchar(50) NOT NULL,
`obj_ridkey` varchar(50) default NULL,
`obj_namekey` varchar(50) default NULL,
`obj_fullnameobj` varchar(50) default NULL,
`obj_fullnamekey` varchar(50) default NULL,
`obj_versionkey` varchar(50) default NULL,
`obj_orderkey` varchar(50) default NULL,
`obj_type` varchar(50) NOT NULL default 'normal',
`obj_pktype` varchar(50) NOT NULL default 'normal',
`obj_base` varchar(50) default NULL,
`obj_audited` tinyint(3) NOT NULL default '0',
`obj_printable` tinyint(3) NOT NULL default '0',
`obj_order` int(11) NOT NULL default '0',
PRIMARY KEY (`obj_id`),
UNIQUE KEY `unique_tablename` (`obj_tablename`),
KEY `mdl_id` (`mdl_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.CREATE TABLE `tsd_objpropaccess` (
`id` int(11) NOT NULL auto_increment,
`acs_id` varchar(50) NOT NULL default 'default',
`obj_id` varchar(50) NOT NULL,
`tfa_fieldname` varchar(50) NOT NULL,
`tfa_readable` tinyint(4) NOT NULL default '1',
`tfa_editable` tinyint(4) NOT NULL default '0',
`tfa_order` int(11) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_order` (`tfa_order`),
KEY `obj_id` (`obj_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
然后我进行操作
ALTER TABLE tsd_objpropaccess
ADD CONSTRAINT fk_obj_id FOREIGN KEY (`obj_id`) REFERENCES tsd_objprop (`obj_id`) ON DELETE CASCADE ON UPDATE CASCADE;
能执行,但并没有建立外键,为什么啊?
-> ADD CONSTRAINT fk_obj_id FOREIGN KEY (`obj_id`) REFERENCES tsd_objprop (
`obj_id`) ON DELETE CASCADE ON UPDATE CASCADE;
ERROR 1005 (HY000): Can't create table 'csdn.#sql-aa0_1' (errno: 150)
mysql>表tsd_objprop不存在
ADD CONSTRAINT `fk_obj_id` FOREIGN KEY (`obj_id`) REFERENCES `tsd_object` (`obj_id`) ON DELETE CASCADE ON UPDATE CASCADE;
SHOW CREATE TABLE `tsd_objpropaccess`;
看不到外键添加
-> ADD CONSTRAINT `fk_obj_id` FOREIGN KEY (`obj_id`) REFERENCES `tsd_object
` (`obj_id`) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.75 sec)
Records: 0 Duplicates: 0 Warnings: 0mysql> show create table tsd_objpropaccess;
+-------------------+-----------------------------------------------------------
| Table | Create Table
+-------------------+-----------------------------------------------------------
| tsd_objpropaccess | CREATE TABLE `tsd_objpropaccess` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`acs_id` varchar(50) NOT NULL DEFAULT 'default',
`obj_id` varchar(50) NOT NULL,
`tfa_fieldname` varchar(50) NOT NULL,
`tfa_readable` tinyint(4) NOT NULL DEFAULT '1',
`tfa_editable` tinyint(4) NOT NULL DEFAULT '0',
`tfa_order` int(11) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `unique_order` (`tfa_order`),
KEY `obj_id` (`obj_id`),
CONSTRAINT `fk_obj_id` FOREIGN KEY (`obj_id`) REFERENCES `tsd_object` (`obj_id
`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------------------+-----------------------------------------------------------
1 row in set (0.08 sec)mysql>
首先,与你的SQL语句无关。mysql> show variables like '%fore%';
+--------------------+-------+
| Variable_name | Value |
+--------------------+-------+
| foreign_key_checks | ON |
+--------------------+-------+
1 row in set (0.14 sec)mysql> show variables like '%mode%';
+--------------------------+--------+
| Variable_name | Value |
+--------------------------+--------+
| innodb_autoinc_lock_mode | 1 |
| slave_exec_mode | STRICT |
| sql_mode | |
+--------------------------+--------+
3 rows in set (0.00 sec)mysql>