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 [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW CREATE TABLE providers\G - Original Message - From: Tim Johnson [EMAIL PROTECTED] 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 [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
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 '-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 [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? - Original Message - From: Tim Johnson [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, February 21, 2007 3:35:46 AM (GMT-0500) Auto-Detected Subject: Re: Retrieving foreign keys and references 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 '-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 [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
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 '-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 [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
On Wednesday 21 February 2007 17:43, Rolando Edwards wrote: SHOW INDEXES FROM `providers`; By the way, what version of MySQL are you using ??? 4.0.20 -- Tim Johnson [EMAIL PROTECTED] Palmer, Alaska, USA -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Retrieving foreign keys and references
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 [EMAIL PROTECTED] Palmer, Alaska, USA Alaska Internet Solutions (2 hairy guys in log cabins) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
