Asad,
InnoDB type tables have enforced FOREIGN KEY constraints since 2001. Unfortunately, none of the table types of MySQL yet supports CHECK constraints.
Best regards,
Heikki
.................
List: mysql
Subject: Re: MySQL/InnoDB-4.1.10 is released
From: Asad Habib <ahabib () engin ! umich ! edu>
Date: 2005-02-15 20:00:40
Message-ID: <Pine.GSO.4.58.0502151459050.28688 () lapis ! engin ! umich ! edu>
[Download message RAW]
Are foreign key and other constraints enforced by the db server in this version or is this something that the programmer has to ensure via application logic?
- Asad
On Tue, 15 Feb 2005, Heikki Tuuri wrote:
Hi!
InnoDB is the MySQL table type that supports foreign keys, transactions, non-escalating row-level locking, all SQL-92 transaction isolation levels, multiversion concurrency control, savepoints, multiple tablespaces, and a non-free online binary hot backup tool.
MySQL-4.1.10 is mainly a bugfix release.
Windows users of the my.cnf option innodb_file_per_table should upgrade to this version, because this fixes the bug introduced to the Windows version of 4.1.9, and earlier versions contained the critical bug in innodb_file_per_table.
Functionality added or changed:
* When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the
operating system version at run time and use the fcntl() file flush method
on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X
for internal disk drives, which caused corruption at power outages.
* A shared record lock LOCK_REC_NOT_GAP is now taken for a matching record in the foreign key check because inserts can be allowed into gaps.
* Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog is used and isolation level of the transaction is not serializable. InnoDB uses consistent read in these cases for a selected table.
Bugs fixed:
* Fixed a bug introduced in 4.1.9 to the Windows version if you used innodb_file_per_table. mysqld would stop and complain about Windows error number 87 in a file operation. (Bug #8021)
* Corrected the handling of trailing spaces in the ucs2 character set. (Bug
#7350)
* Use native tmpfile() function on Netware. All InnoDB temporary files are created under sys:\tmp. Previously, InnoDB temporary files were never deleted on Netware.
* Fix a race condition that could cause the assertion space->n_pending_flushes == 0 to fail in fil0fil.c, in @code{fil_space_free()}, in DROP TABLE or in ALTER TABLE.
* ALTER TABLE ... ADD CONSTRAINT PRIMARY KEY ... complained about bad foreign key definition. (Bug #7831)
* Fix a theoretical hang over the adaptive hash latch in InnoDB if one runs
INSERT ... SELECT ... (binlog not enabled), or a multi-table UPDATE or
DELETE, and only the read tables are InnoDB type, the rest are MyISAM. (Bug
#7879)
Upgrading to 4.1.10:
* If you have created or used InnoDB tables with TIMESTAMP columns in MySQL
versions 4.1.0-4.1.3, you have to rebuild those tables when you upgrade to
MySQL-4.1.4 or later. The storage format in those MySQL versions for a
TIMESTAMP column was wrong. If you upgrade from 4.0 to 4.1.4 or later, then
no rebuild of TIMESTAMP tables is needed.
* If you have stored characters < ASCII(32) to non-latin1 non-BINARY indexed
columns in MySQL versions <= 4.1.2, then you have to rebuild those tables
after you upgrade to >= 4.1.3. The reason is that the sorting order of those
characters and the space character changes for some character sets in 4.1.3.
See the MySQL/InnoDB-4.1.3 changelog for a precise description of the cases
where you need to rebuild the table. Also MyISAM tables have to be rebuilt
or repaired in these cases.
* If you have used column prefix indexes on UTF-8 columns or other multibyte
character set columns in 4.1.0 - 4.1.5, you have to rebuild the tables when
you upgrade to 4.1.6 or later.
* If you have used accent characters (ASCII codes >= 128) in database names,
table names, constraint names, or column names in versions < 4.1, you cannot
upgrade to 4.1 directly, because 4.1 uses UTF-8 to store metadata names. Use
RENAME TABLE to overcome this if the accent character is in the table name
or the database name, or rebuild the table.
Best regards,
Heikki Tuuri
Innobase Oy
http://www.innodb.com
-- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
