How to Add a Primary Key to a MySQL Table

In another post, I showed how to create a new table with a single-column primary key. This example shows how to add a multi-column primary key to an existing table: 

mysql> create table andrews_names

    -> (first_name varchar(10),

    ->  surname    varchar(10));

Query OK, 0 rows affected (0.06 sec)

 

mysql> alter table andrews_names

    -> add primary key

    -> (first_name, surname);

Query OK, 0 rows affected (0.30 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Bloggs');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Joe', 'Reid');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_names

    -> values ('Andrew', 'Reid');

ERROR 1062 (23000): Duplicate entry 'Andrew-Reid' for key 1

mysql> select *from andrews_names;

+------------+---------+

| first_name | surname |

+------------+---------+

| Andrew     | Bloggs  |

| Andrew     | Reid    |

| Joe        | Reid    |

+------------+---------+

3 rows in set (0.01 sec)

 

mysql>

Renaming a Column in MySQL

You can rename a column as shown below. You need to include the column’s definition, even if you are not changing it, otherwise MySQL displays an error:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.09 sec)

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col1  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.01 sec)

 

mysql> alter table andrews_table

    -> change col1 col2 varchar(5);

Query OK, 0 rows affected (0.04 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+------------+------+-----+---------+-------+

| Field | Type       | Null | Key | Default | Extra |

+-------+------------+------+-----+---------+-------+

| col2  | varchar(5) | YES  |     | NULL    |       |

+-------+------------+------+-----+---------+-------+

1 row in set (0.06 sec)

 

mysql>

 

And you can change a column’s definition at the same time, if you wish:

 

mysql> alter table andrews_table

    -> change col2 col3 varchar(10);

Query OK, 0 rows affected (0.18 sec)

Records: 0  Duplicates: 0  Warnings: 0

 

mysql> desc andrews_table;

+-------+-------------+------+-----+---------+-------+

| Field | Type        | Null | Key | Default | Extra |

+-------+-------------+------+-----+---------+-------+

| col3  | varchar(10) | YES  |     | NULL    |       |

+-------+-------------+------+-----+---------+-------+

1 row in set (0.00 sec)

 

mysql>

Modifying Column Definitions in MySQL

If you change a column definition, MySQL converts the data in that column for you. If it finds data which it cannot convert, it displays a warning and you can see details by using the show warnings command. In the example, a varchar column is converted to int. MySQL is unable to convert the value ABC so it sets it to zero instead:

 

mysql> create table andrews_table

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.05 sec)

 

mysql> insert into andrews_table

    -> values ('ABC');

Query OK, 1 row affected (0.00 sec)

 

mysql> insert into andrews_table

    -> values ('123');

Query OK, 1 row affected (0.00 sec)

 

mysql> alter table andrews_table

    -> change col1 col1 int;

Query OK, 2 rows affected, 1 warning (0.00 sec)

Records: 2  Duplicates: 0  Warnings: 0

(N.B. I have reformatted the output from the show warnings command to make it fit on the page.)

mysql> show warnings;

+---------+------+-----------------------------------+

| Level   | Code | Message                           |

+---------+------+-----------------------------------+

| Warning | 1366 | Incorrect integer value:          |
|         |      | 'ABC' for column 'col1' at row 1  |

+---------+------+-----------------------------------+

1 row in set (0.00 sec)

 

mysql> select * from andrews_table;

+------+

| col1 |

+------+

|    0 |

|  123 |

+------+

2 rows in set (0.00 sec)

 

mysql>

Renaming a Table in MySQL

Here are 2 different ways to do this: 

mysql> create table old_name

    -> (col1 varchar(5));

Query OK, 0 rows affected (0.03 sec)

 

mysql> alter table old_name

    -> rename to new_name;

Query OK, 0 rows affected (0.01 sec)

 

mysql> rename table new_name to old_name;

Query OK, 0 rows affected (0.00 sec)

 

mysql>

MySQL BLACKHOLE Engine (again)

My previous post about this was run on a MySQL installation on UNIX. I decided to have another go using the MySQL on my home PC, which is installed on Windows XP. First I had a look for the have_blackhole_engine variable but I could not find it:
  

mysql> SHOW VARIABLES LIKE 'have_blackhole_engine';
Empty set (0.00 sec)

mysql>

But I decided to carry on and created a table using the BLACKHOLE engine:

mysql> use test

Database changed

mysql> create table andrew (col1 varchar(10));
Query OK, 0 rows affected (0.41 sec)

mysql> alter table andrew engine = blackhole;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Then I inserted a row into the table and looked for it but it had gone so this time it had worked as expected:

mysql> insert into andrew values ('Fred');
Query OK, 1 row affected (0.00 sec)

mysql> select * from andrew;
Empty set (0.00 sec)

mysql>