Noah,
I tested the script with 4.1.11. As expected, the first CREATE gives errno 150.
You should create the two other tables first.
heikki@hundin:~/mysql-4.1/client> ./mysql test
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.11-debug-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql> CREATE TABLE vir_label_set_fields
-> (
-> index_name VARCHAR(128) NOT NULL,
-> label_set_name VARCHAR(128) NOT NULL,
-> field_name VARCHAR(128) NOT NULL,
-> label_type VARCHAR(16) NOT NULL,
-> field_order INTEGER NOT NULL,
-> field_type VARCHAR(8) NOT NULL,
-> display_size INTEGER NOT NULL,
-> input_method VARCHAR(16) NOT NULL,
-> display_name VARCHAR(255),
-> description TEXT,
-> field_required CHAR(1) DEFAULT 'N' ,
-> is_hidden CHAR(1) DEFAULT 'N' ,
-> is_sortable CHAR(1) DEFAULT 'N' ,
-> INDEX vir_label_set_fields_idx_pk (index_name, label_set_name, field_name
, label_type),
-> INDEX vir_label_set_fields_idx_inln (index_name, label_set_name),
-> INDEX vir_label_set_fields_idx_lt (label_type),
-> INDEX vir_label_set_fields_idx_ft (field_type),
-> INDEX vir_label_set_fields_idx_im (input_method),
-> CONSTRAINT vir_label_set_fields_pk
-> PRIMARY KEY (index_name, label_set_name, field_name, label_type),
-> CONSTRAINT vir_lsf_ls_idx_fk
-> FOREIGN KEY(index_name, label_set_name)
-> REFERENCES vir_label_sets(index_name, name) ON DELETE CASCADE,
-> CONSTRAINT vir_label_set_type_fk
-> FOREIGN KEY (label_type)
-> REFERENCES vir_valid_label_types(label_type),
-> CONSTRAINT vir_field_type_fk
-> FOREIGN KEY (field_type)
-> REFERENCES vir_valid_field_types(field_type),
-> CONSTRAINT vir_field_input_fk
-> FOREIGN KEY (input_method)
-> REFERENCES vir_valid_input_methods(input_method),
-> CONSTRAINT vir_lsf_check_required
-> CHECK( field_required IN( 'Y', 'N') ),
-> CONSTRAINT vir_lsf_check_hidden
-> CHECK( is_hidden IN( 'Y', 'N') ),
-> CONSTRAINT vir_lsf_check_sortable
-> CHECK( is_sortable IN( 'Y', 'N') )
-> )
-> TYPE=INNODB
-> ;
ERROR 1005 (HY000): Can't create table './test/vir_label_set_fields.frm' (errno:
150)
mysql>
mysql> CREATE TABLE vir_valid_field_types
-> (
-> field_type VARCHAR(8) NOT NULL,
-> description VARCHAR(200),
-> INDEX vir_valid_field_types_idx (field_type),
-> CONSTRAINT vir_valid_field_types_pk PRIMARY KEY(field_type)
-> )
-> TYPE=INNODB
-> ;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> INSERT INTO vir_valid_field_types VALUES ('STR', 'Indicates default virag
e string field');
can select multiple VALUES from a list of acceptable values');Query OK, 1 row a
ffected (0.03 sec)
mysql> INSERT INTO vir_valid_field_types VALUES ('INT', 'Indicates default virag
e int field');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vir_valid_field_types VALUES ('FLOAT', 'Indicates default vir
age float field');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vir_valid_field_types VALUES ('DATE', 'Indicates default vira
ge date field');
Query OK, 1 row affected (0.00 sec)
mysql>
mysql> CREATE TABLE vir_valid_input_methods
-> (
-> input_method VARCHAR(16) NOT NULL,
-> description VARCHAR(200),
-> INDEX vir_valid_input_methods_idx (input_method),
-> CONSTRAINT vir_valid_input_methods_pk
-> PRIMARY KEY(input_method)
-> )
-> TYPE=INNODB
-> ;
Query OK, 0 rows affected, 1 warning (0.02 sec)
mysql>
mysql> INSERT INTO vir_valid_input_methods VALUES ('ENTRY', 'User can enter supp
ly any value they choose');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vir_valid_input_methods VALUES ('SELECT', 'User selects from
a list of acceptable values');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vir_valid_input_methods VALUES ('EDIT_SELECT', 'User can supp
ly any value, but are provided with a list of VALUES to choose from');
Query OK, 1 row affected (0.00 sec)
mysql> INSERT INTO vir_valid_input_methods VALUES ('MULTI_SELECT', 'User can sel
ect multiple VALUES from a list of acceptable values');
Query OK, 1 row affected (0.00 sec)
Best regards,
Heikki
Oracle Corp./Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables
http://www.innodb.com/order.php