| Subject: | Primary key info incorrect |
The database handle methods primary_key_info and primary_key seem to
return more columns than just the primary key (perhaps foreign keys as
well).
Test Perl script:
#!/usr/local/bin/perl
use DBD::mysql;
use Data::Dumper;
my $dsn = 'dbi:mysql:flock2_build';
my $dbh = DBI->connect($dsn, 'root', '') || die DBI::errstr;
my $sth = $dbh->primary_key_info(undef, undef, 'groups');
print Dumper($sth->fetchall_arrayref);
I tried against a table with the following definition:
CREATE TABLE `groups` (
`group_id` int(11) NOT NULL auto_increment COMMENT 'Unique identifier',
`usernumber` int(11) NOT NULL COMMENT 'the owner of the group',
`group_status_code` varchar(10) NOT NULL COMMENT 'the status of the
group (who can access it)',
`network_id` int(11) default NULL COMMENT 'university that the group
belongs to',
`group_name` varchar(255) default NULL COMMENT 'The name of the group',
`description` text COMMENT 'description of the group',
PRIMARY KEY (`group_id`),
KEY `group_name_n1` (`group_name`,`network_id`),
KEY `group_university_fk` (`network_id`),
KEY `group_user_owner_fk` (`usernumber`),
KEY `group_status_fk` (`group_status_code`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;
ALTER TABLE `groups`
ADD CONSTRAINT `group_status_fk` FOREIGN KEY (`group_status_code`)
REFERENCES `group_status` (`group_status_code`),
ADD CONSTRAINT `group_university_fk` FOREIGN KEY (`network_id`)
REFERENCES `network` (`network_id`),
ADD CONSTRAINT `group_user_owner_fk` FOREIGN KEY (`usernumber`)
REFERENCES `user_authentication` (`usernumber`);
And dumping the primary_key_info returns:
$VAR1 = [
[
undef,
undef,
'groups',
'group_name',
'1',
'group_name_n1'
],
[
undef,
undef,
'groups',
'group_id',
'1',
'PRIMARY'
],
[
undef,
undef,
'groups',
'group_status_code',
'1',
'group_status_fk'
],
[
undef,
undef,
'groups',
'usernumber',
'1',
'group_user_owner_fk'
],
[
undef,
undef,
'groups',
'network_id',
'1',
'group_university_fk'
]
Tested with DBD::mysql 4.003, MySQL 5.0.22 on Windows and DBD::mysql
4.001, MySQL 5.0.15 on Linux