Hi Programmers,
after this legal statement
ALTER TABLE EMPLOYEE TYPE = InnoDB;
I get
Warnings: 0
and still MySQL claims to have to use Type = MyISAM,
Which has never supported Foreign Key constraints.
There must be a workaround for this,
My queries are unambiguous, and MySQL does not obey.
Is this fixed in later releases, please ?
How can I increase the levels and sensitivity of the warnings, please ?
Yours Sincerely
Morten Gulbrandsen
===
C:\mysql\bin>type constraint.sql
USE company;
DESCRIBE EMPLOYEE;
SHOW CREATE TABLE EMPLOYEE;
ALTER TABLE EMPLOYEE TYPE = InnoDB;
SHOW CREATE TABLE EMPLOYEE;
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE"
C:\mysql\bin>mysql -vvv < Constraint.sql > out.txt
C:\mysql\bin>type out.txt
--------------
DESCRIBE EMPLOYEE
--------------
+----------+---------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+----------+---------------+------+-----+---------+-------+
| SUPERSSN | varchar(9) | YES | | NULL | |
| DNO | int(11) | | | 1 | |
| SSN | varchar(9) | | PRI | | |
| FNAME | varchar(15) | | | | |
| MINIT | char(1) | YES | | NULL | |
| LNAME | varchar(15) | | | | |
| BDATE | date | YES | | NULL | |
| ADDRESS | varchar(30) | YES | | NULL | |
| SEX | char(1) | YES | | NULL | |
| SALARY | decimal(10,2) | YES | | NULL | |
+----------+---------------+------+-----+---------+-------+
10 rows in set (0.00 sec)
--------------
SHOW CREATE TABLE EMPLOYEE
--------------
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
| Table | Create Table
|
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
| EMPLOYEE | CREATE TABLE `employee` (
`SUPERSSN` varchar(9) default NULL,
`DNO` int(11) NOT NULL default '1',
`SSN` varchar(9) NOT NULL default '',
`FNAME` varchar(15) NOT NULL default '',
`MINIT` char(1) default NULL,
`LNAME` varchar(15) NOT NULL default '',
`BDATE` date default NULL,
`ADDRESS` varchar(30) default NULL,
`SEX` char(1) default NULL,
`SALARY` decimal(10,2) default NULL,
PRIMARY KEY (`SSN`)
) TYPE=MyISAM |
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
1 row in set (0.00 sec)
--------------
ALTER TABLE EMPLOYEE TYPE = InnoDB
--------------
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
--------------
SHOW CREATE TABLE EMPLOYEE
--------------
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
| Table | Create Table
|
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
| EMPLOYEE | CREATE TABLE `employee` (
`SUPERSSN` varchar(9) default NULL,
`DNO` int(11) NOT NULL default '1',
`SSN` varchar(9) NOT NULL default '',
`FNAME` varchar(15) NOT NULL default '',
`MINIT` char(1) default NULL,
`LNAME` varchar(15) NOT NULL default '',
`BDATE` date default NULL,
`ADDRESS` varchar(30) default NULL,
`SEX` char(1) default NULL,
`SALARY` decimal(10,2) default NULL,
PRIMARY KEY (`SSN`)
) TYPE=MyISAM |
+----------+------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
------------------------------------------------------------------------
--------
----------------------------------------------+
1 row in set (0.00 sec)
--------------
SHOW TABLE STATUS FROM company LIKE "EMPLOYEE"
--------------
+----------+--------+------------+------+----------------+-------------+
--------
---------+--------------+-----------+----------------+------------------
---+----
-----------------+------------+----------------+---------+
| Name | Type | Row_format | Rows | Avg_row_length | Data_length |
Max_dat
a_length | Index_length | Data_free | Auto_increment | Create_time
| Upd
ate_time | Check_time | Create_options | Comment |
+----------+--------+------------+------+----------------+-------------+
--------
---------+--------------+-----------+----------------+------------------
---+----
-----------------+------------+----------------+---------+
| employee | MyISAM | Dynamic | 0 | 0 | 0 |
42
94967295 | 1024 | 0 | NULL | 2003-07-31
16:06:51 | 200
3-07-31 16:06:51 | NULL | | |
+----------+--------+------------+------+----------------+-------------+
--------
---------+--------------+-----------+----------------+------------------
---+----
-----------------+------------+----------------+---------+
1 row in set (0.00 sec)
Bye
C:\mysql\bin>
===
-----Ursprüngliche Nachricht-----
Von: Fred van Engen [mailto:[EMAIL PROTECTED]
Gesendet: Donnerstag, 31. Juli 2003 14:59
An: Morten Gulbrandsen
Cc: [EMAIL PROTECTED]
Betreff: Re: I Specify explicitly TYPE = InnoDB But My SQL gives me
TYPE=MyISAM ?
Hi,
On Thu, Jul 31, 2003 at 02:46:06PM +0200, Morten Gulbrandsen wrote:
> According to the manual :
>
> "7.5.5.2 Foreign Key Constraints
> Starting from version 3.23.43b InnoDB features foreign key
constraints.
> InnoDB is the first MySQL table type which allows you to define
foreign
> key constraints to guard the integrity of your data."
>
TYPE = InnoDB only works if MySQL is compiled with InnoDB support,
which is the default in binaries from MySQL. If you compile it yourself
you need to configure --with-innodb .
Regards,
Fred.
--
Fred van Engen XB Networks B.V.
email: [EMAIL PROTECTED] Televisieweg 2
tel: +31 36 5462400 1322 AC Almere
fax: +31 36 5462424 The Netherlands
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
