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.
This post was published by Daniyal Hamid. Daniyal currently works as the Head of Engineering in Germany and has 20+ years of experience in software engineering, design and marketing. Please show your love and support by sharing this post.