To only copy the column attributes of another table, without copying over any indexes or data, you can do the following:
- Use the
CREATE TABLE ... SELECT
statement, as it does not automatically copy indexes from the original table; - Specify a falsy condition in the
WHERE
clause of theSELECT
statement, so it doesn't copy over any rows.
In this way, you will have the same column attributes, but none of the indexes or rows will be copied over.
To demonstrate this, let's suppose you have the following MySQL/MariaDB table:
CREATE TABLE user (
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25) NOT NULL,
PRIMARY KEY (id),
CONSTRAINT UQ_Name UNIQUE (name)
);
INSERT INTO user(name)
VALUES ('john'), ('david'), ('john'), ('john'), ('wayne'), ('david');
You can use the MySQL/MariaDB CREATE TABLE ... SELECT
statement to create an empty table with only the column attributes copied over, for example, in the following way:
CREATE TABLE user_copy (
SELECT *
FROM user
WHERE 1 = 0;
);
To check the columns and indexes in the new table, you can use any of the following statements (which are equivalent):
DESCRIBE user_copy;
SHOW COLUMNS FROM user_copy;
It would produce an output like the following:
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | No | | 0 | |
| name | varchar(25) | No | | (null) | |
+-------+-------------+------+-----+---------+-------+
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.