Page 1 of 1

reference definition for column in phpmyadmin

Posted: Mon Apr 25, 2005 5:57 am
by newmember
example of use of reference definition for column:
http://dev.mysql.com/doc/mysql/en/examp ... -keys.html

i can't find an option for that :confused:
as i get it.. it's a must have option if i want to relate tables (vital for effiency)

someone knows where is it?

Posted: Mon Apr 25, 2005 7:00 am
by newmember
i tried to manualy run query:
ALTER TABLE mydb.mytable ADD fieldname INT REFERENCES mydb.sometable (somefield)

query succeded but i don't see that addional information about fieldname nowhere.

if i export mytable then "REFERENCES mydb.sometable (somefield)" doesn't appear in output...

Posted: Mon Apr 25, 2005 7:34 am
by phpScott
are you sure you are using the right table type
In MySQL 3.23.44 and up, InnoDB tables support checking of foreign key constraints. See Chapter 15, The InnoDB Storage Engine. See also Section 1.5.5.5, “Foreign Keys”.
This only works as far as I can see on InnoDB's

under the structure tab there should be a type column that will tell you the table type.

phpScott

Posted: Mon Apr 25, 2005 7:43 am
by newmember
i'm not talking here about foreign keys...
look at the link a gave.

anyway i'll quote here relevant lines...

...
You don't actually need foreign keys to join two tables. For table types other than InnoDB, the only things MySQL currently doesn't do are 1) CHECK to make sure that the keys you use really exist in the table or tables you're referencing and 2) automatically delete rows from a table with a foreign key definition. Using your keys to join tables works 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 person(id),
PRIMARY KEY (id)
);
...
you see, the shirt table has this line:
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id)
the rest of example:
...
INSERT INTO person VALUES (NULL, 'Antonio Paz');

SELECT @last := LAST_INSERT_ID();

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

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

SELECT @last := LAST_INSERT_ID();

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

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';
finaly, the last query relies on:
REFERENCES person(id)
so i want to know how can i specify similar line with phpmyadmin.

Posted: Mon Apr 25, 2005 8:11 am
by phpScott
yes that examples refer to InnoDB tables.

http://dev.mysql.com/doc/mysql/en/ansi- ... -keys.html
has something that refers to this matter.
MySQL gives database developers the choice of which approach to use. If you don't need foreign keys and want to avoid the overhead associated with enforcing referential integrity, you can choose another table type instead, such as MyISAM. (For example, the MyISAM storage engine offers very fast performance for applications that perform only INSERT and SELECT operations, because the inserts can be performed concurrently with retrievals. See Section 7.3.2, “Table Locking Issues”.)

If you choose not to take advantage of referential integrity checks, keep the following considerations in mind:

*

In the absence of server-side foreign key relationship checking, the application itself must handle relationship issues. For example, it must take care to insert rows into tables in the proper order, and to avoid creating orphaned child records. It must also be able to recover from errors that occur in the middle of multiple-record insert operations.
I just quoted the relevant section for this discussion but read the whole article as it also gives the pros and cons of what you are trying to achieve.

phpScott

Posted: Mon Apr 25, 2005 8:35 am
by newmember
phpScott
thanks i got it now....
owner SMALLINT UNSIGNED NOT NULL REFERENCES person(id)
is equavivalent to
owner SMALLINT UNSIGNED NOT NULL
...
FOREIGN KEY (owner) REFERENCES person(id)
took me some time to figure that out... my total lack of expirience with sql:)