Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>I would guess that your default storage engine is MyISAM, which ignores foreign key constraints. It silently accepts the declaration of a foreign key, but does not store the constraint or enforce it subsequently.</p> <p>However, it does implicitly create an index on the columns you declared for the foreign key. In MySQL, "<code>KEY</code>" is a synonym for "<code>INDEX</code>". That's what's being shown in the DESCRIBE output: an index, but not a constraint.</p> <p>You are able to insert invalid values to the table right now because there is no constraint. To get a constraint that enforces referential integrity, you must use the InnoDB storage engine:</p> <pre><code>CREATE TABLE actions ( A_id int NOT NULL AUTO_INCREMENT, ... CONSTRAINT fk_Question FOREIGN KEY (Q_id) REFERENCES questions(P_id), CONSTRAINT fk_User FOREIGN KEY (U_id) REFERENCES users(P_id) ) ENGINE=InnoDB; </code></pre> <p>I've always thought it was a big mistake on MySQL's part to <em>silently</em> ignore foreign key constraint declarations. There's no error or warning that the storage engine doesn't support them.</p> <p>The same is true for CHECK constraints. By the way no storage engine used with MySQL supports CHECK constraints but the SQL parser accepts them with no complaint.</p> <hr> <p>The errno 150 issue occurs when it cannot create the InnoDB table, because it couldn't make sense of the foreign key constraint. You can get some more information with:</p> <pre><code>SHOW ENGINE INNODB STATUS; </code></pre> <p>Some requirements for InnoDB foreign keys:</p> <ul> <li>Referenced table must also be InnoDB.</li> <li>Referenced table must have an index and a primary key.</li> <li>SQL data types of FK column and referenced PK column must be identical. For example, INT does not match BIGINT or INT UNSIGNED.</li> </ul> <hr> <p>You can change the storage engine of a table that has data in it:</p> <pre><code>ALTER TABLE actions ENGINE=InnoDB; </code></pre> <p>This effectively copies the entire MyISAM table to an InnoDB table, then once that succeeds it drops the MyISAM table and renames the new InnoDB table to the name of the former MyISAM table. This is called a "table restructure" and it can be time-consuming, depending on how much data is in the table. A table restructure occurs during ALTER TABLE, even in some cases where it may seem unnecessary.</p> <hr> <p>Re your update 2:</p> <blockquote> <p>I'm told that it's important to enforce data integrity with functional foreign keys, but also that InnoDB should not be used with MySQL. What do you recommend?</p> </blockquote> <p>Who told you that? It's absolutely false. <a href="http://www.mysqlperformanceblog.com/2007/01/08/innodb-vs-myisam-vs-falcon-benchmarks-part-1/" rel="nofollow noreferrer">InnoDB has better performance than MyISAM</a> (though InnoDB needs more attention to <a href="http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/" rel="nofollow noreferrer">tuning the configuration</a>), InnoDB supports atomic changes, transactions, foreign keys, and InnoDB is much more resistant to corrupting data in a crash.</p> <p>Unless you're running an old, unsupported version of MySQL (5.0 or earlier) you should use InnoDB as your <em>default</em> storage engine choice, and use MyISAM only if you can demonstrate a specific workload that benefits from MyISAM.</p>
    singulars
    1. This table or related slice is empty.
    1. POForeign key not working in MySQL: Why can I INSERT a value that's not in the foreign column?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USAbdul Rafi
    1. USBill Karwin
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for this answer -- saved me a TON of time. By the way, to change the default from MyISAM to InnoDB add "default-storage-engine = innodb" to your my.cnf file under the [mysqld] section.
      singulars
      1. PO
      1. USHDave
    2. CO@HDave: Glad to help! Yes, in recent versions of MySQL there's no reason not to make innodb the default, and in MySQL 5.5 it is.
      singulars
      1. PO
      1. USBill Karwin
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload