List:General Discussion« Previous MessageNext Message »
From:Heikki Tuuri Date:May 13 2002 6:07pm
Subject:Re: InnoDB Foreign Key Constraints
View as plain text  
Daniel,

----- Original Message -----
From: "Daniel Rand" <daniel.rand@stripped>
Newsgroups: mailing.database.mysql
Sent: Monday, May 13, 2002 5:24 PM
Subject: InnoDB Foreign Key Constraints


> Hi,
>
> Does anyone know if it's possible to set up a foreign key constraint where
> one table references the primary key of another table in a different
> database (both tables being of InnoDB type).

yes, you can use the databasename.tablename syntax:

heikki@hundin:~/mysql/client> mysql test
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 3.23.51-log

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> show create table aaa;
+-------+-------------------------------------------------------------------
----
-------------------------------------------------------------------+
| Table | Create Table
                                                                   |
+-------+-------------------------------------------------------------------
----
-------------------------------------------------------------------+
| aaa   | CREATE TABLE `aaa` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`),
  KEY `b` (`b`)
) TYPE=InnoDB |
+-------+-------------------------------------------------------------------
----
-------------------------------------------------------------------+
1 row in set (0.01 sec)

mysql> use test11
Database changed
mysql> create table mmm (a int not null, b int, primary key (a), index (b),
fore
ign key (b) references test.aaa (a));
Query OK, 0 rows affected (0.00 sec)

mysql> show create table mmm;
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------+
| Table | Create Table

                                    |
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------+
| mmm   | CREATE TABLE `mmm` (
  `a` int(11) NOT NULL default '0',
  `b` int(11) default NULL,
  PRIMARY KEY  (`a`),
  KEY `b` (`b`),
  FOREIGN KEY (`b`) REFERENCES `test.aaa` (`a`)
) TYPE=InnoDB |
+-------+-------------------------------------------------------------------
----
----------------------------------------------------------------------------
----
------------------------------------+
1 row in set (0.00 sec)

mysql>


> Thanks,
>
> DAN

Best regards,

Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, hot backup, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from http://www.mysql.com



Thread
Re: InnoDB Foreign Key ConstraintsHeikki Tuuri13 May