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.