Enter password: ******** Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 Server version: 5.5.14 MySQL Community Server (GPL) Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> connect openjcsdb Connection id: 5 Current database: openjcsdb mysql> mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope); ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_a' (errno: 150) mysql> mysql> desc instanceproperties; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ObjectName | varchar(40) | NO | PRI | NULL | | | InstanceName | varchar(40) | NO | PRI | NULL | | | Scope | varchar(80) | NO | PRI | GLOBAL | | | PropertyName | varchar(40) | NO | PRI | NULL | | | ValueType | varchar(255) | YES | | TEXT | | | Value | varchar(255) | YES | | NULL | | | Locked | tinyint(1) | YES | | 0 | | | LockingPID | int(11) | YES | MUL | 0 | | | Permissions | varchar(40) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 9 rows in set (0.03 sec) mysql> desc propertyperms -> ; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ObjectName | varchar(40) | NO | PRI | NULL | | | InstanceName | varchar(40) | NO | PRI | NULL | | | Scope | varchar(80) | NO | PRI | GLOBAL | | | PropertyName | varchar(40) | NO | PRI | NULL | | | ToScope | varchar(80) | NO | PRI | GLOBAL | | | Permissions | varchar(40) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec) mysql> desc instanceproperties; +--------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+--------------+------+-----+---------+-------+ | ObjectName | varchar(40) | NO | PRI | NULL | | | InstanceName | varchar(40) | NO | PRI | NULL | | | Scope | varchar(80) | NO | PRI | GLOBAL | | | PropertyName | varchar(40) | NO | PRI | NULL | | | ValueType | varchar(255) | YES | | TEXT | | | Value | varchar(255) | YES | | NULL | | | Locked | tinyint(1) | YES | | 0 | | | LockingPID | int(11) | YES | MUL | 0 | | | Permissions | varchar(40) | YES | | NULL | | +--------------+--------------+------+-----+---------+-------+ 9 rows in set (0.06 sec) mysql> drop table instanceproperties; Query OK, 0 rows affected (0.04 sec) mysql> create table instanceproperties (ObjectName varchar(40), -> InstanceName varchar(40), -> Scope varchar(80), -> PropertyName varchar(40), -> ValueType varchar(255), -> Value varchar(255), -> Locked tinyint(1), -> LockingPID int(11), -> Permissions varchar(40)); Query OK, 0 rows affected (0.02 sec) mysql> alter table instanceproperties add primary key (ObjectName, InstanceName, Scope, PropertyName); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope); ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_5' (errno: 150) mysql> drop table instanceproperties; Query OK, 0 rows affected (0.01 sec) mysql> create table instanceproperties (ObjectName varchar(40), -> InstanceName varchar(40), -> Scope varchar(80), -> PropertyName varchar(40), -> ValueType varchar(255), -> Value varchar(255), -> Locked tinyint(1), -> LockingPID int(11), -> Permissions varchar(40)); Query OK, 0 rows affected (0.01 sec) mysql> alter table instanceproperties add primary key (ObjectName, InstanceName, Scope, PropertyName); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc propertyperms -> ; +--------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +--------------+-------------+------+-----+---------+-------+ | ObjectName | varchar(40) | NO | PRI | NULL | | | InstanceName | varchar(40) | NO | PRI | NULL | | | Scope | varchar(80) | NO | PRI | GLOBAL | | | PropertyName | varchar(40) | NO | PRI | NULL | | | ToScope | varchar(80) | NO | PRI | GLOBAL | | | Permissions | varchar(40) | YES | | NULL | | +--------------+-------------+------+-----+---------+-------+ 6 rows in set (0.02 sec) mysql> drop table propertyperms; Query OK, 0 rows affected (0.03 sec) mysql> create table propertyperms (ObjectName varchar(40), -> InstanceName varchar(40), -> Scope varchar(80), -> PropertyName varchar(40), -> ToScope varchar(80), -> Permissions varchar(40)); Query OK, 0 rows affected (0.01 sec) mysql> alter table propertyperms add primary key (ObjectName, InstanceName, Scope, PropertyName,ToScope,Permissions); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope); ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_5' (errno: 150) mysql> alter table propertyperms engine=InnoDB; Query OK, 0 rows affected (0.17 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table instanceproperties engine=InnoDB; Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> alter table propertyperms add foreign key (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope); ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_5' (errno: 150) mysql> show create table instanceproperties; +--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ | instanceproperties | CREATE TABLE `instanceproperties` ( `ObjectName` varchar(40) NOT NULL DEFAULT '', `InstanceName` varchar(40) NOT NULL DEFAULT '', `Scope` varchar(80) NOT NULL DEFAULT '', `PropertyName` varchar(40) NOT NULL DEFAULT '', `ValueType` varchar(255) DEFAULT NULL, `Value` varchar(255) DEFAULT NULL, `Locked` tinyint(1) DEFAULT NULL, `LockingPID` int(11) DEFAULT NULL, `Permissions` varchar(40) DEFAULT NULL, PRIMARY KEY (`ObjectName`,`InstanceName`,`Scope`,`PropertyName`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +--------------------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------ ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- -------------------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0.01 sec) mysql> show create table propertyperms; +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ | Table | Create Table | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ | propertyperms | CREATE TABLE `propertyperms` ( `ObjectName` varchar(40) NOT NULL DEFAULT '', `InstanceName` varchar(40) NOT NULL DEFAULT '', `Scope` varchar(80) NOT NULL DEFAULT '', `PropertyName` varchar(40) NOT NULL DEFAULT '', `ToScope` varchar(80) NOT NULL DEFAULT '', `Permissions` varchar(40) NOT NULL DEFAULT '', PRIMARY KEY (`ObjectName`,`InstanceName`,`Scope`,`PropertyName`,`ToScope`,`Permissions`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- ------------------------------------------------------------------------------------+ 1 row in set (0.00 sec) mysql> alter table propertyperms add constraint propertyperms_fk1 foreign key instanceproperties_propertyperms_fk1 (InstanceName,ObjectName,PropertyName,Scope) references instanceproperties (InstanceName,ObjectName,PropertyName,Scope); ERROR 1005 (HY000): Can't create table 'openjcsdb.#sql-5c4_5' (errno: 150) mysql> mysql> desc information_schema.key_column_usage; +-------------------------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------------------------+--------------+------+-----+---------+-------+ | CONSTRAINT_CATALOG | varchar(512) | NO | | | | | CONSTRAINT_SCHEMA | varchar(64) | NO | | | | | CONSTRAINT_NAME | varchar(64) | NO | | | | | TABLE_CATALOG | varchar(512) | NO | | | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | COLUMN_NAME | varchar(64) | NO | | | | | ORDINAL_POSITION | bigint(10) | NO | | 0 | | | POSITION_IN_UNIQUE_CONSTRAINT | bigint(10) | YES | | NULL | | | REFERENCED_TABLE_SCHEMA | varchar(64) | YES | | NULL | | | REFERENCED_TABLE_NAME | varchar(64) | YES | | NULL | | | REFERENCED_COLUMN_NAME | varchar(64) | YES | | NULL | | +-------------------------------+--------------+------+-----+---------+-------+ 12 rows in set (0.03 sec) mysql> select * from information_schema.key_column_usage where table_schema="openjcsdb" and table_name in ("propertyperms","instanceproperties"); +--------------------+-------------------+-----------------+---------------+--------------+--------------------+--------------+------------------+-------------------------------+------------ -------------+-----------------------+------------------------+ | CONSTRAINT_CATALOG | CONSTRAINT_SCHEMA | CONSTRAINT_NAME | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | POSITION_IN_UNIQUE_CONSTRAINT | REFERENCED_ TABLE_SCHEMA | REFERENCED_TABLE_NAME | REFERENCED_COLUMN_NAME | +--------------------+-------------------+-----------------+---------------+--------------+--------------------+--------------+------------------+-------------------------------+------------ -------------+-----------------------+------------------------+ | def | openjcsdb | PRIMARY | def | openjcsdb | instanceproperties | ObjectName | 1 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | instanceproperties | InstanceName | 2 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | instanceproperties | Scope | 3 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | instanceproperties | PropertyName | 4 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | ObjectName | 1 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | InstanceName | 2 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | Scope | 3 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | PropertyName | 4 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | ToScope | 5 | NULL | NULL | NULL | NULL | | def | openjcsdb | PRIMARY | def | openjcsdb | propertyperms | Permissions | 6 | NULL | NULL | NULL | NULL | +--------------------+-------------------+-----------------+---------------+--------------+--------------------+--------------+------------------+-------------------------------+------------ -------------+-----------------------+------------------------+ 10 rows in set (0.04 sec) mysql> show indexes in propertyperms; +---------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +---------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | propertyperms | 0 | PRIMARY | 1 | ObjectName | A | 0 | NULL | NULL | | BTREE | | | | propertyperms | 0 | PRIMARY | 2 | InstanceName | A | 0 | NULL | NULL | | BTREE | | | | propertyperms | 0 | PRIMARY | 3 | Scope | A | 0 | NULL | NULL | | BTREE | | | | propertyperms | 0 | PRIMARY | 4 | PropertyName | A | 0 | NULL | NULL | | BTREE | | | | propertyperms | 0 | PRIMARY | 5 | ToScope | A | 0 | NULL | NULL | | BTREE | | | | propertyperms | 0 | PRIMARY | 6 | Permissions | A | 0 | NULL | NULL | | BTREE | | | +---------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 6 rows in set (0.00 sec) mysql> show indexes in instanceproperties; +--------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | instanceproperties | 0 | PRIMARY | 1 | ObjectName | A | 0 | NULL | NULL | | BTREE | | | | instanceproperties | 0 | PRIMARY | 2 | InstanceName | A | 0 | NULL | NULL | | BTREE | | | | instanceproperties | 0 | PRIMARY | 3 | Scope | A | 0 | NULL | NULL | | BTREE | | | | instanceproperties | 0 | PRIMARY | 4 | PropertyName | A | 0 | NULL | NULL | | BTREE | | | +--------------------+------------+----------+--------------+--------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec) mysql>