Foreign key

Acute · #1 (**permalink**) 01-08-11, 14:33

Hi everyone,
is it good to use explicitly foreign keys? what if i'm sure that my php will handle that things?
do foreign keys affect on db efficiency?

Thnx

r937 · #2 (**permalink**) 01-08-11, 16:18

Quote:

Originally Posted by Acute

is it good to use explicitly foreign keys?

absolutely yes

Quote:

Originally Posted by Acute

what if i'm sure that my php will handle that things?

you should still use foreign keys anyway

php may not be the only way that you will ever want to enter information -- you might want to do data loads, for example

Quote:

Originally Posted by Acute

do foreign keys affect on db efficiency?

sort of

they don't have any effect on SELECTs, but INSERTs and DELETEs are a very tiny bit slower

what would you prefer -- an operation that is a tiny bit slower but guaranteed to result in correct data, or an operation that is a tiny bit faster but might result in incorrect data?

Acute · #3 (**permalink**) 01-08-11, 22:16

Is it possible to use foreign keys in MyISAM

r937 · #4 (**permalink**) 01-08-11, 22:31

Quote:

Originally Posted by Acute

Is it possible to use foreign keys in MyISAM

well, not exactly "use" -- you can define them but they are ignored

Quote:

Originally Posted by da manual

InnoDB tables support checking of foreign key constraints. See Section 13.6, “The InnoDB Storage Engine”. Note that the FOREIGN KEY syntax in InnoDB is more restrictive than the syntax presented for the CREATE TABLE statement at the beginning of this section: The columns of the referenced table must always be explicitly named. InnoDB supports both ON DELETE and ON UPDATE actions on foreign keys. For the precise syntax, see Section 13.6.5.4, “FOREIGN KEY Constraints”.

For other storage engines, MySQL Server parses and ignores the FOREIGN KEY and REFERENCES syntax in CREATE TABLE statements.

-- http://dev.mysql.com/doc/refman/5.5/...ate-table.html

Acute · #5 (**permalink**) 01-08-11, 22:49

So no need to even define them if they are ignored, right?

Internet Services

Web Development

Digital Marketing

Consumer Tech