about InnoDB storage engine

[orange08]

hi, i'm new to MySQL and need help here while creating my first table.

 

i need to use foreign key to connect among my tables, so is that i need to set the my table storage engine to InnoDB in order to use this foreign key feature?

 

thanks!

 

 

[Mchl]

As of now InnoDB is the only MySQL GA storage engine that supports foreign keys.

 

Be aware however, that foreign key functionality can also be programmed outside of database (but InnoDB is still a good choice)

[orange08]

As of now InnoDB is the only MySQL GA storage engine that supports foreign keys.

 

Be aware however, that foreign key functionality can also be programmed outside of database (but InnoDB is still a good choice)

 

thanks. but, is that InnoDB not support FULLTEXT index? i need this index for my search function, so what can i do in this case?

[Mchl]

That's right. Only MyISAM supports FULLTEXT now.

 

I'd say, use MyISAM for tables that really need FULLTEXT, InnoDB for others. Don't define FK restraints in MySQL, but use your script to enforce them.

[orange08]

That's right. Only MyISAM supports FULLTEXT now.

 

I'd say, use MyISAM for tables that really need FULLTEXT, InnoDB for others. Don't define FK restraints in MySQL, but use your script to enforce them.

 

sorry, i'm not understand with 'use your script to enforce them.', can you give me an example?

 

let's say my table1 need FULLTEXT, so it should be MyISAM, then it will link to table2 foreign key. as foreign key is in table2(table1's primary key), so it should be InnoDB, right?

[Mchl]

Foreign keys are used for two things.

 

1. To define relations between tables

2. To define restrictions on data in tables

 

 

Ad 1.

Imagine there's

table `users` with fields `userID`, `name`

table `posts` with fields `postID`, `userID`, `title`, `content`

 

You can define a foreign key to indicate, that `userID` in `posts` is indicating a user from `users` table (by `userID` of course)

However you don't need FOREIGN KEY definition for that to work. It is enough, that when you select data from database you use proper JOIN criteria.

 

SELECT `name`, `title`, `content` FROM `posts` INNER JOIN `users` USING `userID` WHERE `postID` = ?

 

Ad 2.

Imagine you delete a user form `users` table, and you also want to delete all his posts from `posts` table.

When you have FOREIGN KEY restraints defined, MySQL does this for you. You can however do this yourself without foreign keys defined

 

DELETE FROM `users` WHERE `userID` = ?

DELETE FROM `posts` WHERE `userID` = ?

 

Not as comfortable as using foreign keys, but will work as well.

[orange08]

Foreign keys are used for two things.

 

1. To define relations between tables

2. To define restrictions on data in tables

 

 

Ad 1.

Imagine there's

table `users` with fields `userID`, `name`

table `posts` with fields `postID`, `userID`, `title`, `content`

 

You can define a foreign key to indicate, that `userID` in `posts` is indicating a user from `users` table (by `userID` of course)

However you don't need FOREIGN KEY definition for that to work. It is enough, that when you select data from database you use proper JOIN criteria.

 

SELECT `name`, `title`, `content` FROM `posts` INNER JOIN `users` USING `userID` WHERE `postID` = ?

 

Ad 2.

Imagine you delete a user form `users` table, and you also want to delete all his posts from `posts` table.

When you have FOREIGN KEY restraints defined, MySQL does this for you. You can however do this yourself without foreign keys defined

 

DELETE FROM `users` WHERE `userID` = ?

DELETE FROM `posts` WHERE `userID` = ?

 

Not as comfortable as using foreign keys, but will work as well.

 

i'm a bit confuse here, if i'm didn't define foreign key restraint which is Ad 2, then i don't need to use InnoDB on my table that contains foreign key?

[Mchl]

FOREIGN KEY can only be defined between TWO InnoDB tables. So both of them would have to be InnoDB for it work.

[orange08]

FOREIGN KEY can only be defined between TWO InnoDB tables. So both of them would have to be InnoDB for it work.

 

but, how to define foreign key? from my ebook, i seem didn't see the define of foreign key...

 

can i do this:

 

CREATE TABLE IF NOT EXISTS customer(

cust_id varchar(10),

cust_name varchar(20),

company varchar(50),

:

:

PRIMARY KEY (cust_id)

)

 

CREATE TABLE IF NOT EXISTS contract(

contract_id varchar(10),

contract_date date,

cust_id varchar(10),

:

:

PRIMARY KEY (contract_id)

)

 

both tables are MyISAM,

 

can my SELECT statement like this:

 

SELECT * FROM customer, contract

WHERE customer.cust_id = contract.cust_ID

 

in the above case, no foreign key is defined in CREATE TABLE statement...and both are MyISAM, can i use foreign key like this?

 

thanks again!

[Mchl]

Yes you can.

 

Perhaps I confused you a little.

 

Let's start from beginning.

 

In MySQL 'foreign key' might mean two things.

 

1. An abstract meaning: just what you did with your `customer` and `contract` tables. You defined fields in both tables, that relate records from both, but no real database mechanism is involved. The relation is enforced by you using proper queries. This can be used with all types of tables.

 

2. A concrete meaning: InnoDB tables can have FOREIGN KEY defined, that can be used to enforce restraints on data (such as cascading updates or deletes).

 

 

You are after the 1.

[orange08]

Yes you can.

 

Perhaps I confused you a little.

 

Let's start from beginning.

 

In MySQL 'foreign key' might mean two things.

 

1. An abstract meaning: just what you did with your `customer` and `contract` tables. You defined fields in both tables, that relate records from both, but no real database mechanism is involved. The relation is enforced by you using proper queries. This can be used with all types of tables.

 

2. A concrete meaning: InnoDB tables can have FOREIGN KEY defined, that can be used to enforce restraints on data (such as cascading updates or deletes).

 

 

You are after the 1.

 

yup, mine is the 1st case. now i get what you meant, i'll try it out later and come here again if i meet with problem.

 

really appreciate your help, thanks!