How to Copy Unique Rows Into New MySQL Table and Add UNIQUE Constraint?

Let's suppose you have the following MySQL/MariaDB table:

+----+--------+
| id |  name  |
+----+--------+
|  1 |  john  |
|  2 |  david |
|  3 |  john  |
|  4 |  john  |
|  5 |  wayne |
|  6 |  david |
+----+--------+

You can create this table like so:

CREATE TABLE user (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    PRIMARY KEY (id)
);

INSERT INTO user(name)
VALUES ('john'), ('david'), ('john'), ('john'), ('wayne'), ('david');

Depending on your use case, you can:

• Copy Structure and Unique Rows to New Table and Add UNIQUE Constraint;
• Copy Only Unique Rows to New Table and Add UNIQUE Constraint;
• Show Table Structure and Manually Execute Resulting CREATE TABLE Statement.

Copy Structure and Unique Rows to New Table and Add UNIQUE Constraint

You can use the CREATE TABLE ... LIKE statement to create an empty table based on another table. The new table would have the same structure (i.e. same column attributes and indexes as defined in the original table). After that you can add the UNIQUE constraint to the new table, and copy over unique values from the original table. You can see all these steps in the following example:

# STEP 1: Create new table with same structure and constraints as original
CREATE TABLE user_copy LIKE user;

# STEP 2: Add unique constraint
ALTER TABLE user_copy ADD UNIQUE(name);

# STEP 3: Copy data from original table
INSERT INTO user_copy(name) (
    SELECT user.name
    FROM user
    GROUP BY user.name
);

This would result in the following:

SELECT * FROM user_copy;

+----+--------+
| id |  name  |
+----+--------+
|  1 |  david |
|  2 |  john  |
|  3 |  wayne |
+----+--------+

Please note that CREATE TABLE ... LIKE does not preserve any DATA DIRECTORY or INDEX DIRECTORY table options that were specified for the original table, or any foreign key definitions.

Copy Only Unique Rows to New Table and Add UNIQUE Constraint

You can use CREATE TABLE ... SELECT to create a new table from another, where the SELECT statement at the end of CREATE TABLE can be used to copy rows from the original table.

The CREATE TABLE ... SELECT statement does not automatically create any indexes by design. This is to make the statement flexible. However, you can still specify indexes before the SELECT statement. Consider for example, the following:

CREATE TABLE user_copy (
    id INT NOT NULL AUTO_INCREMENT,
    PRIMARY KEY (id),
    UNIQUE (name)
) (
    SELECT name
    FROM user
    GROUP BY name
);

This would result in something like the following:

+----+--------+
| id |  name  |
+----+--------+
|  1 |  david |
|  2 |  john  |
|  3 |  wayne |
+----+--------+

Show Table Structure and Manually Execute Resulting CREATE TABLE Statement

If you're able to manually execute CREATE TABLE statement, then you can use the following query to output the complete CREATE TABLE statement for the original table:

SHOW CREATE TABLE user

This would result in something like the following:

+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                                                                      |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| user  | CREATE TABLE `user` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=7 DEFAULT CHARSET=utf8 |
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------+

You can then modify the displayed CREATE TABLE statement and add the UNIQUE constraint, and manually run it, for example, like so:

CREATE TABLE user_copy (
    id INT NOT NULL AUTO_INCREMENT,
    name VARCHAR(25) NOT NULL,
    PRIMARY KEY (id),
    CONSTRAINT UQ_Name UNIQUE (name)
);

You will, of course, need to copy the data over from the original table as well. You can do so, using either of the following statements:

INSERT INTO user_copy(name) SELECT user.name FROM user GROUP BY user.name;

INSERT IGNORE INTO user_copy(name) SELECT user.name FROM user;

The difference between the two statements is that GROUP BY sorts rows in ascending order (based on the values in the group column) by default, while INSERT IGNORE will go with the default ordering.