0

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!

  • 3
    I've never needed composite keys before, but that doesn't look right to me. First, I'd use unsigned (positive only) numeric values only. How would you determine a key that consists out of two different types? Also: Are you really, really, really sure you need composite keys? I want to question that. But please just file an edit and explain us what you are trying to do (not only "I want a table", but the full story). It could be that you are facing a XY Problem. – kaiser Mar 8 '15 at 21:15
  • Consider @kaiser's comment, after all he said, »... really, really, really ...« - BTW not joking here. Aside from that, not sure if any of your methods actually are suitable to create a composite key, not really a MySQL expert though, but if I remember correctly you need some thing like CONSTRAINT constraint_name UNIQUE ( column_one, column_two ), then you could make constraint_name your primary key. Or something along that line at least. – Nicolai Mar 8 '15 at 22:26
  • I very much doubt that you need composite keys at all here – Tom J Nowell Mar 8 '15 at 22:45
2

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.