Hi:
Below is the following relevant create table syntax
CREATE Table providers(
[snipped]
FOREIGN KEY (status) REFERENCES provider_status(ID,title),
UNIQUE KEY ID (ID)
) TYPE=MyISAM;

and below is the relevant output from a describe query:
mysql> show columns from providers;
+---------------+---------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra
+---------------+---------------+------+-----+------------+----------------+
| status | int(6) | YES | | NULL |
+---------------+---------------+------+-----+------------+----------------+

Is there a syntax equivalent of 'describe' that can show me the references?
thanks
tim
--
Tim Johnson <tim@johnsons-web.com>
Palmer, Alaska, USA

SHOW CREATE TABLE providers\G

----- Original Message -----
From: "Tim Johnson" <tim@johnsons-web.com>
To: "MySQL General Mailing List" <mysql@lists.mysql.com>
Sent: Wednesday, February 21, 2007 2:59:35 AM (GMT-0500) Auto-Detected
Subject: Retrieving foreign keys and references

Hi:
Below is the following relevant create table syntax
CREATE Table providers(
[snipped]
FOREIGN KEY (status) REFERENCES provider_status(ID,title),
UNIQUE KEY ID (ID)
) TYPE=MyISAM;

and below is the relevant output from a describe query:
mysql> show columns from providers;
+---------------+---------------+------+-----+------------+----------------+
| Field | Type | Null | Key | Default | Extra
+---------------+---------------+------+-----+------------+----------------+
| status | int(6) | YES | | NULL |
+---------------+---------------+------+-----+------------+----------------+

Is there a syntax equivalent of 'describe' that can show me the references?
thanks
tim
--
Tim Johnson <tim@johnsons-web.com>
Palmer, Alaska, USA

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=redwards@swmx.com

Hi Tim,

Foreign key definitions are parsed but ignored by MyISAM tables.

Try InnoDB or PBXT (http://www.primebase.com/xt)

On Feb 21, 2007, at 9:35 AM, Tim Johnson wrote:

> On Wednesday 21 February 2007 17:01, Rolando Edwards wrote:
>> SHOW CREATE TABLE providers\G
> Hi Rolando:
> That doesn't do it on my machine (linux, ver 4.0.2)
> Here's what I see
> mysql> SHOW CREATE TABLE providers\G
> *************************** 1. row ***************************
> Table: providers
> Create Table: CREATE TABLE `providers` (
> `ID` int(11) NOT NULL auto_increment,
> `name` varchar(80) NOT NULL default '**',
> `nick_name` varchar(10) NOT NULL default '**',
> `email_address` varchar(80) NOT NULL default '**',
> `start_date` date NOT NULL default '0000-00-00',
> `company_ID` int(11) NOT NULL default '0',
> `status` int(6) default NULL,
> `modified` timestamp(10) NOT NULL,
> UNIQUE KEY `ID` (`ID`)
> ) TYPE=MyISAM
> 1 row in set (0.00 sec)
>
> As you can see there is no reference info for column `status`
> thanks
> tim
>
> --
> Tim Johnson <tim@johnsons-web.com>
> Palmer, Alaska, USA
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?
> unsub=paul.mccullagh@primebase.com
>

On Wednesday 21 February 2007 17:51, Paul McCullagh wrote:
> Hi Tim,
Hello Paul
> Foreign key definitions are parsed but ignored by MyISAM tables.
Understood. Thanks
> Try InnoDB or PBXT (http://www.primebase.com/xt)
for the time being, I'm going to stick with MyISAM. I've
got a possible solution I will try later - and the target is
a online accounting system for a *very* small company (mine)
with a *very* small number of clients.

Here we go - it's weird, but it might work.
I use a default value, which is itself a foreign key that
point to a "meta-table"
example
status int(6) default 1,
1 is the ID for a table with columns `ID` & 'descriptor`, where the
desciptor field holds something like this "provider_status.ID.title"
the script executes the "show cols" query, then queries the "metadata"
table where "provider_status.ID.title" tells the script to pull
`ID' and `title' from provider_status and load the values into a
select/option list form entity.

I'm sure that this will not be optimal performance-wise, but where speed
is not an issue, will save much coding time.
Since it will be the weekend before I try this out, I welcome observations
and any references to meta-data approaches using MySQL /MyISAM.

Thanks to everybody for the quick responses.
regards
--
Tim Johnson <tim@johnsons-web.com>
Palmer, Alaska, USA
Alaska Internet Solutions (2 hairy guys in log cabins)