I created a table "purchase" by following SQL:
(acturally I copied code somewhere and modified it)
CREATE TABLE IF NOT EXISTS purchase
(
custid VARCHAR(20) NOT NULL,
discript VARCHAR(50),
INDEX IND_KEY(custid),
FOREIGN KEY(custid) REFERENCES customer(custid) ON DELETE CASCADE
)TYPE = INNODB;
the table has been created (table main exists).
if I delete line "INDEX IND_KEY(custid)," in SQL above, the SQL can not be executed properly (error).
I am wondering that why I must use an INDEX for foreign key?
it looks useless, what is an INDEX used for?
can you explain?
in phpMyAdmin, I can not see an undersore in field "custid", what does undersore indecate in phpMyAdmin?
thanks
what does an INDEX mean?
Moderator: General Moderators
I'm not a MySQL expert, but I beleive it uses to for two things
1) it enforces the UNIQUENESS contraint on the primary key (I beleive KEY and INDEX are synonomous in MySQL).
2) it generates a faster data-access method, allow the database to avoid a full table scan one many operations, leading to big boosts in selects, requirely slightly more work on insert/updates/deletes.
I suspect its 1) that breaking stuff completely.
1) it enforces the UNIQUENESS contraint on the primary key (I beleive KEY and INDEX are synonomous in MySQL).
2) it generates a faster data-access method, allow the database to avoid a full table scan one many operations, leading to big boosts in selects, requirely slightly more work on insert/updates/deletes.
I suspect its 1) that breaking stuff completely.