I’m trying to add a column to one of of my database tables, but there is a syntax error
and I can’t seem to find the problem…
My current database table looks like this:
component + tag_id + item_id
------------|----------|-----------
com_content | 23 | 2642
com_content | 26 | 3481
com_content | 35 | 1868
com_content | 85 | 5827
com_content | 89 | 7882
I want it to look like this, where ‘id’ is auto increment and all columns part of the primary key
id + component + tag_id + item_id
-----|--------------|----------|-----------
1 | com_content | 23 | 2642
2 | com_content | 26 | 3481
3 | com_content | 35 | 1868
4 | com_content | 85 | 5827
5 | com_content | 89 | 7882
This is my query:
DROP PRIMARY KEY
ALTER TABLE gitags_items
ADD COLUMN id INT NOT NULL AUTO_INCREMENT FIRST
PRIMARY KEY (id,component,tag_id,item_id)
However I’m getting this error message:
#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 'PRIMARY KEY ALTER TABLE gitags_items ADD COLUMN id INT NOT NULL AUTO_INC' at line 1
Any help/pointers would be much appreciated
You cannot alter a primary key as it is referenced as a foreign key in other table. This is because of the referential integrity constraint.
Referential Integrity Although the main purpose of a foreign key constraint is to control the data that can be stored in the foreign
key table, it also controls changes to data in the primary key table.The constraint enforces referential integrity by guaranteeing that changes cannot be made to data in the primary key table if those changes invalidate the link to data in the foreign key table. If an attempt is made to delete the row in a primary key table or to change a primary key value, the action will fail when the deleted or changed primary key value corresponds to a value in the foreign key constraint of another table. To successfully change or delete a row in a foreign key constraint, you must first either delete the foreign key data in the foreign key table or change the foreign key data in the foreign key table, which links the foreign key to different primary key data.Cascading Referential Integrity
By using cascading referential integrity constraints, you can define
the actions that the Database Engine takes when a user tries to delete
or update a key to which existing foreign keys point. The following
cascading actions can be defined.NO ACTION
The Database Engine raises an error and the delete or update action on
the row in the parent table is rolled back.CASCADE
Corresponding rows are updated or deleted in the referencing table
when that row is updated or deleted in the parent table. CASCADE
cannot be specified if a timestamp column is part of either the
foreign key or the referenced key. ON DELETE CASCADE cannot be
specified for a table that has an INSTEAD OF DELETE trigger. ON UPDATE
CASCADE cannot be specified for tables that have INSTEAD OF UPDATE
triggers.SET NULL
All the values that make up the foreign key are set to NULL when the
corresponding row in the parent table is updated or deleted. For this
constraint to execute, the foreign key columns must be nullable.
Cannot be specified for tables that have INSTEAD OF UPDATE triggers.SET DEFAULT
All the values that make up the foreign key are set to their default
values if the corresponding row in the parent table is updated or
deleted. For this constraint to execute, all foreign key columns must
have default definitions. If a column is nullable, and there is no
explicit default value set, NULL becomes the implicit default value of
the column. Cannot be specified for tables that have INSTEAD OF UPDATE
triggers.CASCADE, SET NULL, SET DEFAULT and NO ACTION can be combined on tables
that have referential relationships with each other. If the Database
Engine encounters NO ACTION, it stops and rolls back related CASCADE,
SET NULL and SET DEFAULT actions. When a DELETE statement causes a
combination of CASCADE, SET NULL, SET DEFAULT and NO ACTION actions,
all the CASCADE, SET NULL and SET DEFAULT actions are applied before
the Database Engine checks for any NO ACTION.
Your post intrigued me. There is a «workaround» here, but I was curious, so I performed the following tests. You might like to try what worked for me below before trying the workaround. If there are FOREIGN KEYs, and potential conflicts (duplicates and/or KEY violations), then this won’t work. It may be the reason for your error.
I created a table (bill) as follows.
mysql> CREATE TABLE bill(fred INT, paul VARCHAR(10));
Query OK, 0 rows affected (0.31 sec)
Then inserted some sample values.
mysql> INSERT INTO bill VALUES(1, 'adsfd');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO bill VALUES(2, 'adsfd');
Query OK, 1 row affected (0.05 sec)
mysql> INSERT INTO bill VALUES(3, 'xxxx');
Query OK, 1 row affected (0.05 sec)
I then performed a SHOW CREATE TABLE My_TableG (the G gives clear output that can be cut and pasted).
mysql> SHOW CREATE TABLE billG
*************************** 1. row ***************************
Table: bill
Create Table: CREATE TABLE `bill` (
`fred` int(11) DEFAULT NULL,
`paul` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
Just to see the results of my INSERTs
mysql> SELECT * FROM bill;
+------+-------+
| fred | paul |
+------+-------+
| 1 | adsfd |
| 2 | adsfd |
| 3 | xxxx |
+------+-------+
3 rows in set (0.00 sec)
mysql>
Now, issue your ALTER TABLE statement.
ALTER TABLE bill MODIFY COLUMN fred INT AUTO_INCREMENT NOT NULL PRIMARY KEY;
Recheck the definition of your table.
mysql> SHOW CREATE TABLE billG
*************************** 1. row ***************************
Table: bill
Create Table: CREATE TABLE `bill` (
`fred` int(11) NOT NULL AUTO_INCREMENT,
`paul` varchar(10) DEFAULT NULL,
PRIMARY KEY (`fred`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
And the field fred is indeed now a PRIMARY KEY and also an AUTO_INCREMENT.
And to check that it’s working.
mysql> INSERT INTO bill (paul) VALUES('yyyy');
Query OK, 1 row affected (0.08 sec)
mysql> SELECT * FROM bill;
+------+-------+
| fred | paul |
+------+-------+
| 1 | adsfd |
| 2 | adsfd |
| 3 | xxxx |
| 4 | yyyy |
+------+-------+
4 rows in set (0.00 sec)
So, it’s also AUTO_INCREMENTing correctly. I’m not sure why your original statement gave the strange ALTER TABLE causes auto_increment resequencing error — maybe a bug of some sort? While performing these tests, I made a number of small errors — MySQL appears to be very fussy about the exact order of steps and the particular syntax of the ALTER TABLE statement.
Facing an error in MySQL Database Server as specified in the title of this article, “Multiple primary key defined” will be discussed in this article itself. This is the actual execution of the SQL Command performed in a MySQL Command Console :
mysql> alter table user_role add id int auto_increment primary key; ERROR 1068 (42000): Multiple primary key defined
The table named ‘user_role’ can be described as follows :
mysql> desc user_role; +---------+---------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+-------+ | id_user | int(11) | NO | PRI | NULL | | | id_role | int(11) | NO | PRI | NULL | | +---------+---------+------+-----+---------+-------+ 2 rows in set (0,01 sec)
The table above has already a primary key on it. The primary key itself is a composite primary key which consists of two columns. In this context, adding another primary key is not allowed. So, in order to add another primary key, it means the table must be altered or to make it more easier, just drop the table and recreate it with just one primary key with the rest of the column is defined as a normal field without the attribute of primary key. To prove it the column has a composite primary key, just execute the following command :
mysql> show create table test; +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | Table | Create Table | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ | test | CREATE TABLE `test` ( `id_user` int(11) NOT NULL, `id_role` int(11) NOT NULL, PRIMARY KEY (`id_user`,`id_role`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +-------+--------------------------------------------------------------------------------------------------------------------------------------------------------------+ 1 row in set (0,01 sec) mysql>
It is obviously shown in the above output :
PRIMARY KEY (`id_user`,`id_role`)
So, the step taken is shown below :
1. Drop the table
mysql> drop table user_role; Query OK, 0 rows affected (0,06 sec) mysql>
2. Recreate the table as shown below :
mysql> create table user_role(id int not null auto_increment,id_user int not null, id_role int not null, primary key(id)); Query OK, 0 rows affected (0,12 sec) mysql>
3. Add foreign key for the id_user column as shown below :
mysql> alter table test add constraint fk_id_user foreign key(id_user) references user(id) on update cascade on delete cascade; Query OK, 0 rows affected (0,07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql>
3. Add foreign key for the id_role column as shown below :
mysql> alter table test add constraint fk_id_role foreign key(id_role) references role(id_role) on update cascade on delete cascade; Query OK, 0 rows affected (0,08 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> desc user_role;
4. Describe the table structure as shown below :
+---------+---------+------+-----+---------+----------------+ | Field | Type | Null | Key | Default | Extra | +---------+---------+------+-----+---------+----------------+ | id | int(11) | NO | PRI | NULL | auto_increment | | id_user | int(11) | NO | MUL | NULL | | | id_role | int(11) | NO | MUL | NULL | | +---------+---------+------+-----+---------+----------------+ 3 rows in set (0,00 sec) mysql>
So, adding the primary key has already been finished upon solving the error message “Multiple primary key defined”.
MySQL возвращает эту ошибку (скорее всего), потому что нет уникального индекса, определенного в столбце id. (MySQL требует, чтобы был уникальный индекс. Другая возможность, которую вы уже поняли, состоит в том, что в таблице может быть только один столбец, определенный как AUTO_INCREMENT.)
Чтобы этот столбец был AUTO_INCREMENT, вы можете добавить ограничение UNIQUE или ограничение PRIMARY KEY в столбце id. Например:
ALTER TABLE `blog` ADD CONSTRAINT `blog_ux` UNIQUE (`id`) ;
(Обратите внимание, что этот оператор возвращает ошибку, если для столбца id существуют одинаковые значения.)
В качестве альтернативы вы можете сделать столбец id основным символом таблицы (если в таблице еще нет ограничения PRIMARY KEY).
ALTER TABLE `blog` ADD PRIMARY KEY (`id`) ;
(Обратите внимание, что этот оператор возвращает ошибку, если для столбца id существует любое дублирующее значение, или если в этом столбце есть значения NULL, если в таблице есть ограничение PRIMARY KEY.)