dbDelta not creating tables with composite keys

Question

I've been trying to figure this one out for 3 days now, reading every reference on dbDelta I can find.

My ultimate goal is to create a table of unique ad_id, ad_trigger pairs.

This does not create a table:

if($wpdb->get_var("SHOW TABLES LIKE '$ad_trigger_table_name'") != $ad_trigger_table_name) {
$sql = "CREATE TABLE " . $ad_trigger_table_name . " (
    ad_id int(12) NOT NULL,
    ad_trigger text NOT NULL,
    PRIMARY KEY  (ad_id,ad_trigger) 
     );";
dbDelta($sql);
}

Nor does this:

$sql = "CREATE TABLE " . $ad_trigger_table_name . " (
    ad_id int(12) NOT NULL,
    ad_trigger text NOT NULL,
    UNIQUE KEY pkey (ad_id,ad_trigger)    
     );";
dbDelta($sql);

This does work -- but does not give me a composite key.

if($wpdb->get_var("SHOW TABLES LIKE '$ad_trigger_table_name'") != $ad_trigger_table_name) {
$sql = "CREATE TABLE " . $ad_trigger_table_name . " (
    id int(12) NOT NULL auto_increment,
    ad_id int(12) NOT NULL,
    ad_trigger text NOT NULL,
    PRIMARY KEY  (id)  
     );";
dbDelta($sql);
}

Any light anyone can shed on this would be great!

Answer 1

Had a similar issue to you. Your problem is the ad_trigger field is a TEXT field. You need to make this a VARCHAR to allow for indexing.

TEXT types are not stored in the table. Instead a pointer is stored in the table and the data is stored elsewhere. While you can create an index for them, I think this process would be too complicated for use with WordPress (reference: StackOverflow: Indexing a MySQL Text Column). You'll have an easier time with the index if you use a standard data type such as VARCHAR.

I'd recommend using the code below. My personal preference is to always include an unique ID field separate from the data since it can help make updates to the table data easier. Also, change the VARCHAR length to something you're sure will work for your data. The code below sets it equivalent to TINYTEXT.

$sql = "CREATE TABLE " . $ad_trigger_table_name . " (
    id INT(12) NOT NULL AUTO_INCREMENT,
    ad_id INT(12) NOT NULL,
    ad_trigger VARCHAR(255) NOT NULL,
    PRIMARY KEY  (id),
    UNIQUE KEY pkey (ad_id,ad_trigger)
    );";

Your other option would be to create some sort of unique id for each trigger (possibly put them in their own table and then reference the trigger id in this table). In that case, I'd suggest the following.

$sql = "CREATE TABLE " . $ad_trigger_table_name . " (
        id INT(12) NOT NULL AUTO_INCREMENT,
        ad_trigger TEXT NOT NULL,
        PRIMARY KEY  (id),
        );";

$sql = "CREATE TABLE " . $ad_id_trigger_relationship_table_name . " (
        id INT(12) NOT NULL AUTO_INCREMENT,
        ad_id INT(12) NOT NULL,
        ad_trigger_id INT(12) NOT NULL,
        PRIMARY KEY  (id),
        UNIQUE KEY pkey (ad_id,ad_trigger_id)
        );";

This would be more inline with "ideal" database practice (i.e. a table contains either specific data (ad triggers) or defines relationships between other tables). For practicality/real-world use, do whatever you're most comfortable with or makes the most sense for your project.

As others stated in comments to your question, you may not need composite keys at all. Since I don't know exactly how you're using this table and data though, I've provided the code as similar to what you posted as possible.

Hope this helps.