is fk?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

is fk?

Post 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,
waskelton4
Forum Contributor
Posts: 132
Joined: Mon Sep 09, 2002 6:42 pm

Post by waskelton4 »

what dbms do you use?

if it is mysql you can run the query..

Code: Select all

show create table `db`.`table`

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
timvw
DevNet Master
Posts: 4897
Joined: Mon Jan 19, 2004 11:11 pm
Location: Leuven, Belgium

Post by timvw »

With tools like http://adodb.sf.net you have the function getMetaForeignKeys() ..
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post 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
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

Post by bimo »

sorry, I didn't see the adodb lib. I'll check it out.

thanks,
User avatar
bimo
Forum Contributor
Posts: 100
Joined: Fri Apr 16, 2004 11:18 pm
Location: MD

MetaForeignKeys

Post 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]
Post Reply