Myisam Table: Composite Key As Substitute For Foreign Key?

[nodirtyrockstar]

My shared web host does not allow for InnoDB tables. I was planning to use them to take advantage of the foreign key constraint. Now I am going to try to make do with MyISAM, and it seems like the next best thing is a composite key. Would you agree with that? Where can I find information about how to correctly set that up? In my research, I keep finding endless information about foreign keys, and less about the concept of composite keys.

 

Any help you can offer would be greatly appreciated. Thank you in advance for your consideration.

[nodirtyrockstar]

Alrighty, so a little more research leads me to believe that a foreign key is a composite key that links two columns across different tables, and a composite key links two columns in the same table. Does that sound right? After doing a little more research, I think I found that I don't need foreign keys after all. Thanks anyway!

[Christian F.]

No, a composite key is not a replacement for a foreign key. They are two distinct constructs, and in fact a composite key is often made up of foreign keys.

While MyISAM doesn't actually support the enforcement of foreign key constraints, you can still use foreign keys. You just cannot rely upon the database to enforce the validity of the references, which means that you may use a foreign key ID which does not exist in the referenced table.

 

This is a situation which shouldn't occur regardless of whether or not foreign key relations are enforced, and is prevented by having a sound logic in your application. So proper error handling is imperative.

Transactions might also be helpful in preventing unsynchronized relations, but that's something you'll have to judge on a case-by-case basis.

[nodirtyrockstar]

I have a basic grasp on foreign key v composite key now, but I am still struggling with this...so maybe you all can help after all.

 

I am developing a low security shopping cart for an online record shop. It is low security because we are not storing, sending, or processing any customer information. There are no user IDs or passwords that pertain to specific customers. Their "carts" will persist in a database which will be referenced by their PHP session ID. There are three tables: products, sessions, and sessProdLnk. Here is the detail for each of them:

 

CREATE TABLE `products` (

`id` varchar(20) NOT NULL,

`artist` varchar(30) NOT NULL,

`title` varchar(30) NOT NULL,

`artwork` varchar(255) NOT NULL,

`label` varchar(30) NOT NULL,

`year` year(4) NOT NULL,

`price` decimal(6,2) NOT NULL,

`qty` int(11) NOT NULL,

`agedOff` tinyint(1) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

CREATE TABLE `sessions` (

`id` varchar(255) NOT NULL,

`created` datetime NOT NULL,

`expired` datetime NOT NULL,

`completed` tinyint(1) NOT NULL,

`price` decimal(6,2) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

CREATE TABLE `sessProdLnk` (

`products_id` varchar(20) NOT NULL,

`sessions_id` varchar(255) NOT NULL,

`qty` int(6) NOT NULL,

KEY `products_id` (`products_id`),

KEY `sessions_id` (`sessions_id`)

) ENGINE=MyISAM DEFAULT CHARSET=latin1

 

Products will contain the inventory, and can only be changed by my client, the site owner. The sessions table contains cart specific data, such as when it was created, when it expires, whether or not the transaction was completed, and the total price of all the items in the cart. The sessProdLnk table contains the product id, the session id, and the quantity of the item. In other words, this table will actually be capable of referencing the list of products and quantities associated with any particular session.

 

Since the product table has no dependency on the other two tables, I am not worried about it. However the price of a cart relates directly to how many products, and the quantities of each product, that are connected to each session.

 

I assume that this is an issue people have faced before. What is the smartest way to enforce referential integrity in this database? Can you refer me to resources or share your knowledge as to best practices given my parameters?

[kicken]

So long as you insert all the right ID's into the right columns, then you won't have an issue with your tables joining up. You'll still be able to get your totals and what not for the cart. You can still implement the concept of a foreign key using MyISAM, the database engine just won't enforce it (ie, rejecting an insert if the foreign key value is bad).

 

If those enforcement capabilities are important to you then you should use a database engine that actually supports it. If that's not an option then you'll have to manage it yourself in your code. There are two ways you can do that:

1) Always check your constraints with PHP before you do any inserts, and make sure you cleanup all the tables on a delete

or

2) Write stored procedures which manage all the details and call the procedures from your PHP

[fenway]

The question is really why you think you need FK constraints.