List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:February 4 2004 7:18am
Subject:Re: column being created as varchar() when char() requested.
View as plain text  
Hi Philip,

> > Hmmm..., it doesn't seem to be a problem for me in 4.0.17:
> >
> >   mysql> CREATE TABLE t3
> >       -> (
> >       ->    id CHAR(5) NOT NULL,
> >       ->    description VARCHAR(48) NOT NULL,
> >       ->    PRIMARY KEY (id)
> >       -> ) TYPE = InnoDB;
> >   Query OK, 0 rows affected (0.02 sec)
> >
> >   mysql> DESC t3;
> >   +-------------+-------------+------+-----+---------+-------+
> >   | Field       | Type        | Null | Key | Default | Extra |
> >   +-------------+-------------+------+-----+---------+-------+
> >   | id          | varchar(5)  |      | PRI |         |       |
> >   | description | varchar(48) |      |     |         |       |
> >   +-------------+-------------+------+-----+---------+-------+
> >   2 rows in set (0.01 sec)
> >
> >   mysql> CREATE TABLE t4
> >       -> (
> >       ->   id INT NOT NULL,
> >       ->   t3_id CHAR(5) NOT NULL,
> >       ->   INDEX t3_ind(t3_id)
> >       -> ) TYPE = InnoDB;
> >   Query OK, 0 rows affected (0.02 sec)
> >
> >   mysql> ALTER TABLE t4 ADD CONSTRAINT FOREIGN KEY (t3_id) REFERENCES
> > t3(id);
> >   Query OK, 0 rows affected (0.11 sec)
> >   Records: 0  Duplicates: 0  Warnings: 0
> >
> > I do get the error you quote (ERROR 1005: Can't create table...) if I
> > leave out the index creation in either table, which is documented in
> > the manual
> > <http://www.mysql.com/doc/en/InnoDB_foreign_key_constraints.html>.  I
> > can see that supp.supp_cd is a PRIMARY KEY.  Did you create the
> > required index on hpi_supp_agmt.supp_cd before you tried to add the
> > foreign key reference?
> >
> > Michael
>
> The hpi_supp_agmt.supp_cd is part of a compound primary index. BTW, this
> is a legacy database I am trying to port. Here is the create for the
> hpi_supp_agmt:
>
> create table hpi_supp_agmt
>  (
>    div_cd char(4) not null,
>    hpi_no char(15) not null,
>    supp_cd char(5) not null,
>    agmt_no char(8) not null,
>    agmt_owner char(4) not null,
>    agmt_price decimal(16) not null,
>    agmt_uom char(4) not null,
>    agmt_lt integer not null,
>    agmt_exp_dt date not null,
>    updt_user_id smallint not null,
>    updt_dt date not null,
>    create_dt date not null,
>    constraint p1hpisuppagmt primary key (div_cd,hpi_no,supp_cd,agmt_no)
>  ) type = InnoDB;

What if you create an additional index on the supp_cd column only?

With regards,

Martijn Tonies
Database Workbench - developer tool for InterBase, Firebird, MySQL & MS SQL
Server.
Upscene Productions
http://www.upscene.com

Thread
column being created as varchar() when char() requested.Philip Walden3 Feb
  • Re: column being created as varchar() when char() requested.Michael Stassen3 Feb
    • Re: column being created as varchar() when char() requested.Philip Walden3 Feb
      • Re: column being created as varchar() when char() requested.Michael Stassen3 Feb
        • Re: column being created as varchar() when char() requested.Philip Walden4 Feb
          • Monthly reportingScott Haneda4 Feb
            • Bash script to MySqlScott Haneda4 Feb
              • Re: Bash script to MySqlHassan Schroeder4 Feb
                • Re: Bash script to MySqlBernd Tannenbaum4 Feb
                  • RE: Bash script to MySqlRussell Horn4 Feb
                  • Re: Bash script to MySqlScott Haneda5 Feb
                    • Re: Bash script to MySqlBernd Tannenbaum5 Feb
                    • Re: Bash script to MySqlMichael Stassen5 Feb
                      • Re: Bash script to MySqlScott Haneda6 Feb
                        • Re: Bash script to MySqlMichael Stassen6 Feb
              • Re: Bash script to MySqlDuncan Hill4 Feb
              • User Defined FunctionBácskai Gergő" 4 Feb
          • Re: column being created as varchar() when char() requested.Michael Stassen4 Feb
            • Re: column being created as varchar() when char() requested.Philip Walden5 Feb
              • Re: column being created as varchar() when char() requested.Michael Stassen5 Feb
                • Re: column being created as varchar() when char() requested.Philip Walden6 Feb
                  • Re: column being created as varchar() when char() requested.Michael Stassen6 Feb
  • Re: column being created as varchar() when char() requested.Martijn Tonies4 Feb
RE: Bash script to MySqlDan Muey4 Feb