The additional Features for working with linked Tables have been deactivated

[ryver]

I want to link my tables together with foreign keys but I can't work it out.
On the main screen in PHPMyAdmin I get this message:

The additional Features for working with linked Tables have been deactivated

I am unsure how to remedy this, can someone help me please,

Glen:(

[HalfaBee]

What are you trying to do with foreign keys?

HalfaBee

[ryver]

I simply want to associate the primary key of one table with data in another table.
For example:

For Primary Keys, each group has an ID number and each member has an ID number.

The member must be part of a group and therefore one of the fields in the member table must include a group ID from the Group table.

There doesn't seem to be a function in PHPMyAdmin to link/join the tables together by foreign key relationships. Instead I see the error saying...

"the additional features for working with linked tables have been deactivated"

...is this a problem? or should I just use PHP to validate the existance of the foreign key (use PHP to look up the table to see if the group exists)

[HalfaBee]

Check out the mysql manual.


Using Foreign Keys

In MySQL 3.23.44 and up, InnoDB tables supports checking of foreign key constraints. See InnoDB. See also ANSI diff Foreign Keys.

You don't actually need foreign keys to join 2 tables. The only thing MySQL currently doesn't do (in type types other than InnoDB), is CHECK to make sure that the keys you use really exist in the table(s) you're referencing and it doesn't automatically delete rows from a table with a foreign key definition. If you use your keys like normal, it'll work just fine:

CREATE TABLE person (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
name CHAR(60) NOT NULL,
PRIMARY KEY (id)
);

CREATE TABLE shirt (
id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
PRIMARY KEY (id)
);


INSERT INTO person VALUES (NULL, 'Antonio Paz');

INSERT INTO shirt VALUES
(NULL, 'polo', 'blue', LAST_INSERT_ID()),
(NULL, 'dress', 'white', LAST_INSERT_ID()),
(NULL, 't-shirt', 'blue', LAST_INSERT_ID());


INSERT INTO person VALUES (NULL, 'Lilliana Angelovska');

INSERT INTO shirt VALUES
(NULL, 'dress', 'orange', LAST_INSERT_ID()),
(NULL, 'polo', 'red', LAST_INSERT_ID()),
(NULL, 'dress', 'blue', LAST_INSERT_ID()),
(NULL, 't-shirt', 'white', LAST_INSERT_ID());


SELECT * FROM person;
+----+---------------------+
| id | name |
+----+---------------------+
| 1 | Antonio Paz |
| 2 | Lilliana Angelovska |
+----+---------------------+

SELECT * FROM shirt;
+----+---------+--------+-------+
| id | style | color | owner |
+----+---------+--------+-------+
| 1 | polo | blue | 1 |
| 2 | dress | white | 1 |
| 3 | t-shirt | blue | 1 |
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
| 7 | t-shirt | white | 2 |
+----+---------+--------+-------+


SELECT s.* FROM person p, shirt s
WHERE p.name LIKE 'Lilliana%'
AND s.owner = p.id
AND s.color <> 'white';

+----+-------+--------+-------+
| id | style | color | owner |
+----+-------+--------+-------+
| 4 | dress | orange | 2 |
| 5 | polo | red | 2 |
| 6 | dress | blue | 2 |
+----+-------+--------+-------+


HalfaBee

[ryver]

Yes, I know, I read that but then I got confused with the other Error.
What does it mean? It seemed to indicate that some kind of table linking was possible and for whatever reason, it was deactivated.

I am happy to disregard that error message if I cannot do anything about it but I didn't want to keep working on a database that had features deactivated if those features COULD be activated and especially if this error means that I could run in to more problems in the future.

Have you seen this error on PHPMyAdmin?
It is on the front page when you click on your Database in the lest column.

[HalfaBee]

I just tried a similar query and it worked in mysql-front.

I have not tried phpmyadmin yet.

HalfaBee

[HalfaBee]

I tried it in phpmyadmin and the same query worked.

Its not the latest version of phpmyadmin.

this was the query id did

SELECT cn.* FROM cant_name cn, ebooks eb
WHERE cn.cn_email LIKE eb.eb_email

HalfaBee

[ryver]

OK, that code works, I get it!

but do you see the error I am talking about?

here is a screen dump

http://www.ryver.com/Untitled-1.htm

[Digitalroot]

phpMyAdmin will store "Links" between tables that only work when using phpMyAdmin. It really has nothing to do with any MySQL features.

In the CFG file of phpMyAdmin there is a PMA user who must have read access to the user table and have it own tables setup in order to do this.

All that error is telling you is that the PMA user has not been defined. There is also a setting in the CFG file to turn the error off.