Page 1 of 1
is fk?
Posted: Fri Oct 28, 2005 10:50 am
by bimo
Ï am trying to find if a db key is a foreign key. It seems like you should be able to do this but the info I found didn't work. I also haven't found any functions that would do this yet but it seems like there should be.
Anyone know for sure or have any other ideas?
thanks,
Posted: Fri Oct 28, 2005 11:01 am
by waskelton4
what dbms do you use?
if it is mysql you can run the query..
look near the end for a line similar to..
Code: Select all
CONSTRAINT `tablename_ibfk_1` FOREIGN KEY (`fieldname`) REFERENCES `parentTable` (`fieldName`)
unsure about other database systems
will
Posted: Fri Oct 28, 2005 12:04 pm
by timvw
With tools like
http://adodb.sf.net you have the function getMetaForeignKeys() ..
Posted: Fri Oct 28, 2005 12:43 pm
by bimo
I'm sorry, I meant find if it's a foreign key using php. show tables would work but I am trying not to use regular expressions.
...and you were right, I am using mysql. I found in the manual that MySQL uses SYS_FOREIGN to pass fk references.
How (where) does Innodb store information about foreign keys?
Foreign keys are stored in InnoDB tables SYS_FOREIGN and SYS_FOREIGN_COLS.
InnoDB stores foreign key constraints as separate objects. When we take a table to use, InnoDB searches all foreign key constraints for that table as well as the ones that reference that table (note above that we have secondary indexes on table names). It caches the foreign key information to the data dictionary cache in main memory. Thus, foreign key info is very quickly available for any operation on the table.
(from mysql.com)
I don't know if I can reference MySQL's variables (Or whatever SYS_* is), though. It doesn't seem like I am able to but maybe you know better?
thanks
Posted: Fri Oct 28, 2005 5:10 pm
by bimo
sorry, I didn't see the adodb lib. I'll check it out.
thanks,
MetaForeignKeys
Posted: Sun Oct 30, 2005 11:55 pm
by bimo
Okay, I've been messing around with MetaForeignKeys for a while now and can't figure out why it isn't working. It's probably because I'm not using it right or something but I'm not sure.
in the main manual it says this
but that didn't work. For some reason, the ADOdb class didn't come with the latest version of MetaForeignKeys() for mysql, even though the changelog said it should. I found it, though, at
http://cvs.postnuke.com/viewcvs.cgi/Pos ... p?rev=1.11
I then added it to adodb.inc.php but it still doesn't work - at least it's not returning anything on various tables that should.
Here's my code:
Code: Select all
$table = "person";
$fk = $conn->MetaForeignKeys($table, $owner=false, $upper=false);
if($fk!=false)
print_r($fk);
else print("no way")
and the class function:
Code: Select all
// "Innox - Juan Carlos Gonzalez" <jgonzalez#innox.com.mx>
function MetaForeignKeys( $table, $owner = FALSE, $upper = FALSE, $asociative = FALSE )
{
if ( !empty($owner) )
{
$table = "$owner.$table";
}
$a_create_table = $this->getRow(sprintf('SHOW CREATE TABLE %s', $table));
$create_sql = $a_create_table[1];
$matches = array();
$foreign_keys = array();
if ( preg_match_all("/FOREIGN KEY \(`(.*?)`\) REFERENCES `(.*?)` \(`(.*?)`\)/", $create_sql, $matches) )
{
$num_keys = count($matches[0]);
for ( $i = 0; $i < $num_keys; $i ++ )
{
$my_field = explode('`, `', $matches[1][$i]);
$ref_table = $matches[2][$i];
$ref_field = explode('`, `', $matches[3][$i]);
if ( $upper )
{
$ref_table = strtoupper($ref_table);
}
$foreign_keys[$ref_table] = array();
$num_fields = count($my_field);
for ( $j = 0; $j < $num_fields; $j ++ )
{
if ( $asociative )
{
$foreign_keys[$ref_table][$ref_field[$j]] = $my_field[$j];
} else {
$foreign_keys[$ref_table][] = "{$my_field[$j]}={$ref_field[$j]}";
}
}
}
}
return $foreign_keys;
}
I know that there's not much to my test but I was just trying to see if anything was returned.
Thanks.[/list]