Gerald,
>You can't add constraints referencing indices that don't exist.
>Create your indecies first.
Constraints don't reference indexes, they reference columns.
These columns exist. When the table type is MyISAM, the syntax
is accepted. When it's InnoDB - it fails.
(
This schema was originally on Oracle, where the syntax was valid and
acted on.)
Ian Hartas wrote:
>>Description:
>>
>>
> I have a database schema .sql file which is accepted when run with MyISAM
>tables. If I change the tables to be type = InnoDB, then it fails with the error
>message:
>
>mysql -u ts -p hbs <schema_mysql.sql
>ERROR 1005 at line 54: Can't create table './hbs/#sql-34ef_f.frm' (errno: 150)
>
>
>
>>How-To-Repeat:
>>
>>
> Save this text as schema_mysql.sql and run it as noted in the description
> line.
> database called 'hbs', user called 'ts'
>
>/*
>** MySQL schema for the Holiay Booking System within FJS.
>**
>** by Ian Hartas, Kid01.
>**
>*/
>
>drop table if exists ts_proj_team;
>drop table if exists ts_resource_mgrs;
>drop table if exists ts_entry;
>drop table if exists ts_user_details;
>drop table if exists ts_project_code_details;
>drop table if exists ts_project_details;
>drop table if exists ts_team;
>drop table if exists ts_programme;
>drop table if exists ts_location_entry;
>drop table if exists ts_default_location;
>
>create table ts_team
>(
> TEAM_ID integer(8) not null primary key
> ,TEAM_NAME varchar(30) not null
> ,team_admin_email varchar(100)
>) type = InnoDB;
>
>create table ts_programme
>(
> PROGRAMME_ID integer(8) not null primary key
> ,PROGRAMME_NAME varchar(30) not null
>) type = InnoDB;
>
>create table ts_user_details
>(
> USER_ID varchar(8) not null primary key
> ,TEAM_ID integer(8)
> ,STANDARD_HOURS float(30,2)
> ,USER_NAME varchar(40) not null
> ,PASSWORD varchar(20) not null
> ,DEFAULT_LOCATION_ID integer(8)
> ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N'))
> ,DESCRIPTION varchar(255)
> ,EMAIL_ADDRESS varchar(100)
> ,HOURLY_RATE decimal(9,2)
> ,ACCESS_LEVEL integer(8) default 0
> ,charge_rate varchar(20)
> ,start_date datetime
> ,holiday_entitlement decimal(9,2)
> ,holiday_taken decimal(9,2)
> ,holiday_carry_over decimal(9,2)
> ,holiday_bought_sold decimal(9,2)
> ,holiday_hours varchar(200)
>) type = InnoDB;
>
>alter table ts_user_details add constraint u_t foreign key (team_id)
> references ts_team(team_id);
>
>create table ts_project_details
>(
> PROJECT_ID integer(8) not null primary key
> ,PROJECT_NAME varchar(60) not null
> ,DESCRIPTION varchar(255)
> ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N'))
>) type = InnoDB;
>
>create table ts_project_code_details
>(
> PROJECT_CODE_ID integer(8) not null primary key
> ,PROJECT_ID integer(8) not null
> ,PROJECT_CODE varchar(10) not null
> ,PROJECT_CODE_NAME varchar(60) not null
> ,DESCRIPTION varchar(255)
> ,UTIL_IND CHAR(1)
> ,ACTIVE CHAR(1) -- check(ACTIVE in ('Y','N'))
>) type = InnoDB;
>
>alter table ts_project_code_details add constraint pc_t foreign key (project_id)
> references ts_project_details(project_id);
>
>create table ts_proj_team
>(
> TEAM_ID integer(8) not null
> ,PROJECT_ID integer(8) not null
>) type = InnoDB;
>
>alter table ts_proj_team add constraint pt_p foreign key (project_id)
> references ts_project_details(project_id);
>alter table ts_proj_team add constraint pt_t foreign key (team_id)
> references ts_team(team_id);
>
>create table ts_resource_mgrs
>(
> TEAM_ID integer(8) not null
> ,USER_ID varchar(8) not null
>) type = InnoDB;
>
>alter table ts_resource_mgrs add constraint rm_p foreign key(team_id)
> references ts_team(team_id);
>alter table ts_resource_mgrs add constraint rm_u foreign key(user_id)
> references ts_user_details(user_id);
>
>create table ts_entry
>(
> ID integer(38) not null primary key
> ,USER_ID varchar(8) not null
> ,PROJECT_ID integer(8)
> ,PROJECT_CODE_ID integer(8)
> ,TEAM_ID integer(8)
> ,HOURS FLOAT(30,2)
> ,EDATE datetime
> ,CDATE datetime
> ,MDATE datetime
> ,LOCKED CHAR(1) -- check(LOCKED in ('Y','N','D'))
> ,UPDATE_HOURS FLOAT(30,2)
> ,UPDATE_PROJECT_ID integer(8)
> ,UPDATE_PROJECT_CODE_ID integer(8)
> ,DESCRIPTION varchar(255)
> ,nbhours decimal(9,2)
> ,update_nbhours decimal(9,2)
>) type = InnoDB;
>
>alter table ts_entry add constraint e_p foreign key (project_id)
> references ts_project_details(project_id);
>
>alter table ts_entry add constraint ec_p foreign key (project_code_id)
> references ts_project_code_details(project_code_id);
>
>alter table ts_entry add constraint e_u foreign key (user_id)
> references ts_user_details(user_id);
>
>
>/*
>** Staff movement tracking
>*/
>
>create table ts_location_entry
>(
> USER_ID varchar(8) not null
> ,LDATE datetime
> ,AMPM CHAR(2)
> ,LOCATION varchar(40)
>) type = InnoDB;
>
>create table ts_default_location
>(
> DEFAULT_LOCATION_ID integer(8) not null primary key
> ,DEFAULT_LOCATION varchar(40)
> ,location_country char(3)
>) type = InnoDB;
>
>create index ts_proj_code_id_idx on ts_project_code_details (project_id);
>
>create index ts_proj_team_idx on ts_proj_team (team_id, project_id);
>
>create index ts_entry_index on ts_entry (user_id, edate) ;
>
>create index loc_ind on ts_location_entry (user_id,ldate,ampm) ;
>
>
># create sequence next_default_location_id increment by 1 start with 1000;
>create table next_default_location_id (id INT NOT NULL);
>insert into next_default_location_id values (999);
>
># create sequence next_team_id increment by 1 start with 1000;
>create table next_team_id (id INT NOT NULL);
>insert into next_team_id values (999);
>
># create sequence next_programme_id increment by 1 start with 1000;
>create table next_programme_id (id INT NOT NULL);
>insert into next_programme_id values (999);
>
># create sequence next_project_id increment by 1 start with 1000;
>drop table if exists next_project_id;
>create table next_project_id (id INT NOT NULL);
>insert into next_project_id values (999);
>
># create sequence next_project_code_id increment by 1 start with 1000;
>drop table if exists next_project_code_id;
>create table next_project_code_id (id INT NOT NULL);
>insert into next_project_code_id values (999);
>
># create sequence next_entry_id increment by 1 start with 1000;
>drop table if exists next_entry_id;
>create table next_entry_id (id INT NOT NULL);
>insert into next_entry_id values (999);
>
>
># End of schema
>
>
>
>>Fix:
>>
>>
> None known, other than to remove the "alter table" directives.
>
>
>
>>Submitter-Id: Ian Hartas
>>Originator: root
>>Organization:
>>
>>
>
> Fujitsu Services
> Westfields House, West Avenue, Kidsgrove, Staffordshire, UK.
>
>
>
>>MySQL support: none
>>Synopsis: Syntax accepted with MyISAM is reject by InnoDB - Fails on Linux and
>>Solaris
>>Severity: non-critical
>>Priority: low
>>Category: mysql
>>Class: sw-bug
>>Release: mysql-4.1.0-alpha (Source distribution)
>>Server: /usr/local/bin/mysqladmin Ver 8.40 Distrib 4.1.0-alpha, for pc-linux on i686
>>
>>
>Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
>This software comes with ABSOLUTELY NO WARRANTY. This is free software,
>and you are welcome to modify and redistribute it under the GPL license
>
>Server version 4.1.0-alpha-log
>Protocol version 10
>Connection Localhost via UNIX socket
>UNIX socket /tmp/mysql.sock
>Uptime: 5 days 21 hours 41 min 18 sec
>
>Threads: 1 Questions: 110 Slow queries: 0 Opens: 99 Flush tables: 1 Open tables:
>10 Queries per second avg: 0.000
>
>
>>C compiler: gcc (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
>>C++ compiler: g++ (GCC) 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
>>Environment:
>>
>>
> Linux RedHat 8 and also Solaris 2.6
>System: Linux lifeboat 2.4.18-24.8.0smp #1 SMP Fri Jan 31 06:03:47 EST 2003 i686 i686
>i386 GNU/Linux
>Architecture: i686
>
>Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc
>GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs
>Configured with: ../configure --prefix=/usr --mandir=/usr/share/man
>--infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --
>host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit
>Thread model: posix
>gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7)
>Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS='' ASFLAGS=''
>LIBC:
>lrwxrwxrwx 1 root root 14 Jan 29 14:54 /lib/libc.so.6 ->
>libc-2.2.93.so
>-rwxr-xr-x 1 root root 1235468 Sep 6 2002 /lib/libc-2.2.93.so
>-rw-r--r-- 1 root root 2233342 Sep 5 2002 /usr/lib/libc.a
>-rw-r--r-- 1 root root 178 Sep 5 2002 /usr/lib/libc.so
>Configure command: ./configure
>
>
>
>
>
>
-------- End of forwarded message --------
--
regards,
Ian Hartas
----------------------------------
Why is it that the word "gullible"
isn't in the dictionary?
----------------------------------
-------- End of forwarded message --------
--
regards,
Ian Hartas
---------------------------------------------
"Pluralitas non est ponenda sine neccesitate"
Occam's Razor.
---------------------------------------------
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
