How do I create a UNIQUE KEY when creating wpdb table

The best way to figure out errors like this is to run the SQL query inside phpMyAdmin (or another SQL management tool) and see what the error message(s) is.

In your caes, it gives this error:

#1170 - BLOB/TEXT column 'firstname' used in key specification without a key length

text type columns are not good for indexing as they can hold a lot of data, and what the system is aksing you for is how much of that data you want to include in the index.

If it was me, I’d change the firstname and lastname fields to VARCHAR, as they can be indexed easily, and there’s very little chance of anyone needing more then 255 characters for either name. As well as that, I’d also realyl sugest not having a unique index on the first name field.. think about it, what happens when you want to get two people called ‘Michael’ in there? ??

Thanks for your response. However changing firstname and lastname to VARCHAR makes no difference. As for not making firtname or lastname UNIQUE I would agree. I will probably make the Email unique instead. However this is just a test:

Here is my updated code. See if you can find the mistake:

global $jal_db_version;
$jal_db_version = '1.0';

function jal_install() {
    global $wpdb;
    global $jal_db_version;

    $table_name = $wpdb->prefix . 'simons_table';

    $charset_collate = $wpdb->get_charset_collate();

    $sql = "CREATE TABLE $table_name (
		id mediumint(9) NOT NULL AUTO_INCREMENT,
		time datetime DEFAULT '0000-00-00 00:00:00' NOT NULL,
		firstname VARCHAR NOT NULL,
		lastname VARCHAR NOT NULL,
		description text NOT NULL,
		url varchar(55) DEFAULT '' NOT NULL,
		UNIQUE KEY id (id),
		UNIQUE KEY lastname (lastname)
	) $charset_collate;";

    require_once( ABSPATH . 'wp-admin/includes/upgrade.php' );
    dbDelta( $sql );

    add_option( 'jal_db_version', $jal_db_version );
}

function jal_install_data() {
    global $wpdb;

    $welcome_name = 'Mr. WordPress';
    $welcome_text = 'Congratulations, you just completed the installation!';

    $table_name = $wpdb->prefix . 'simons_table';

    $wpdb->insert(
        $table_name,
        array(
            'time' => current_time( 'mysql' ),
            'name' => $welcome_name,
            'text' => $welcome_text,
        )
    );
}

register_activation_hook( __FILE__, 'jal_install' );
register_activation_hook( __FILE__, 'jal_install_data' );

Have you run your SQL query directly in phpMyAdmin to see what the error message is for this time around?

I did, and it’s very obvious once you see the error.

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'NOT NULL,
        lastname VARCHAR NOT NULL,
        description text NOT NULL,
        url var' at line 4

You should really look into using tools like phpMyAdmin to debug things like this. You will end up saving a huge amount of time because you’ll know what is actually wrong rather then guessing like you are now. ??

I’ve tried adjusting the code. I just get one error after another. Where are you entering the code and what code are you entering exactly? Do you enter the coe under the SQL tab or under the Query tab in PHPmyadmin

Normally I’d do it under the SQL tab.

The “easy” way to do this might help you out though.

Use phpMyAdmin to create the table, set up all of the indexes and everything else that you need. Then export the table – and show the result on screen instead of downloading it. then copy-and-paste the CREATE TABLE query into your script (and set your variables like $tablename correctly). That will let you DROP the table from the database, and have the correct creation syntax for it. You can test this by pasting it back into the SQL query windw in phpMyAdmin, or by just running your plugin.

The problem is I can’t get it working using either method. I will worry about how to export the table once I get it working.

All I did was add an extra line of code and the table is no longer created when I activate the plugin. This is the line that causes the query to no longer work:

UNIQUE KEY lastname (lastname)

Unfortunately nothing works when running the query in phpmyadmin, even the old query which worked in the PHP code.

Don’t use the code that you’ve got when you’re creating a table in phpmyAdmin. Do it all in there manually through that interface. That will make sure that it’s all set up the way that MySQL expects it.

Forget about what you’ve got in your plugin for now as it sounds like you need have a little bit of learning to do when it comes to SQL syntax (and that’s not meant to be rude or mean, but just an observation). Some more reading and experimentation will definately help that out, and that’s where using something like phpMyAdmin to do the initial creation of your tables will come in very very handy – becaues when something doesn’t work it will tell you exctly why instead of having no error messages the way that you’re trying to force it now.

Thanks for your help! I didn’t realize you could create a table manually using PHPmyadmin. I thought you had to enter an SQL query. This is very helpful to know for the future.

It appears I needed to add a Grave accent( ` ) around each column name. I don’t know why this should be the case since they don’t appear in any of the WP codex examples.

I exported query code and added it to the PHP code and it seems to be working.