Sql error 1068 42000 multiple primary key defined - Исправление ошибок и поиск оптимальных решений проблем

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”.

Источник

Содержание

Syntax error or access violation 1068 multiple primary key defined
[5.3.13] artisan migration issue #15788
Comments
Description:
Steps To Reproduce:
#1068 — Multiple primary key defined возникает ошибка при импорте
Сообщения 2
1 Тема от servomech 2015-12-10 10:40:31
Тема: #1068 — Multiple primary key defined возникает ошибка при импорте
Re: #1068 — Multiple primary key defined возникает ошибка при импорте
Сообщения 2
Syntax error or access violation 1068 multiple primary key defined
Русские Блоги
ERROR 1068 (42000): Multiple primary key defined
Интеллектуальная рекомендация
Реализация оценки приложения iOS
JS функциональное программирование (е)
PWN_JarvisOJ_Level1
Установка и развертывание Kubernetes
На стороне многопроцессорного сервера — (2) *

Syntax error or access violation 1068 multiple primary key defined

migration table n

You are probably adding those columns with new migration and the error is happening cause you already have primary column id on that table. Try: n

Schema::create(‘user’, function (Blueprint $table) <n $table->unsignedBigInteger(‘user_id’, 11)->index(); // depends on the version of Laravel you are usingn $table->string(‘username’, 50);n $table->string(‘password’, 50);n>);n n

I found the solution. Sorry it is late reply. n

In this series, for each episode, I’ll answer one Laravel-related question that has cropped up in the Laracasts community. Who knows, maybe your question will be the next one I answer in video form! «,»path»:»/series/laravel-explained»,»strippedBody»:»In this series, for each episode, I’ll answer one Laravel-related question that has cropped up in the Laracasts community. Who knows, maybe your question will be the next one I answer in video form!»,»thumbnail»:»https://ik.imagekit.io/laracasts/series/thumbnails/laravel-explained.png»,»large_thumbnail»:»https://laracasts.s3.amazonaws.com/series/thumbnails/social-cards/laravel-explained.png»,»svgThumbnail»:»https://ik.imagekit.io/laracasts/series/thumbnails/svg/laravel-explained.svg»,»slug»:»laravel-explained»,»episodeCount»:9,»difficultyLevel»:»Advanced»,»customUrl»:null,»version»:null,»version_notes»:null,»complete»:0,»wallpaper»:»»,»archived»:0,»runTime»:»1h 41m»,»taxonomy»:<«name»:»Frameworks»,»path»:»https://laracasts.com/browse/frameworks»>,»hasChapters»:false,»isLarabit»:0,»isCreatorSeries»:0,»progress»:<«started»:false,»completionPercentage»:0,»episodesCompleted»:0,»completed»:false,»nextEpisodePosition»:1>,»lastUpdated»:»Aug 28, 2019″>,<«id»:142,»className»:»Laracasts\Series»,»title»:»Laravel 8 From Scratch»,»body»:»

rnWe don’t learn tools for the sake of learning tools. Instead, we learn them because they help us accomplish a particular goal. With that in mind, in this series, we’ll use the common desire for a blog — with categories, tags, comments, email notifications, and more — as our goal. Laravel will be the tool that helps us get there. Each lesson, geared toward newcomers to Laravel, will provide instructions and techniques that will get you to the finish line. rn rn

Источник

[5.3.13] artisan migration issue #15788

Laravel Version: 5.3.13
PHP Version: 7.0.11 (cli) (built: Sep 21 2016 22:56:53) ( NTS )
Database Driver & Version: Server version: 5.5.42 — Source distribution, Database client version: libmysql — mysqlnd 5.0.12-dev — 20150407

Description:

When I try to run migration (create table and add a composite key) via artisan it fails: it logs an error to the console, creates a table (without this key) and does not create any entry in the migrations table. So it is impossible to rollback.

Error log:

[IlluminateDatabaseQueryException]
SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined (SQL: alter table car_models add primary key car_models_name_year_from_primar y ( name , year_from ))

[PDOException]
SQLSTATE[42000]: Syntax error or access violation: 1068 Multiple primary key defined

I believe artisan should rollback the migration on failure by default. Fix me if I’m wrong, please.

Steps To Reproduce:

The migration php file:

The console command:
php artisan migrate

The text was updated successfully, but these errors were encountered:

You can manually delete the table from your database and then you can migrate again. Laravel inserts the record into the migrations table only after the migration is complete.

The issue here is that you are using increments which creates an incrementing primary integer key, and then you try to create another primary key. You can only have one primary key. I suspect you want to use index instead of primary .

@dwightwatson I want to use unique instead of primary , I have already fixed it. Thanks nevertheless.

My main point is that we cannot roll back a migration containing errors, so we have to reverse everything manually (in my case it was a table drop). If it is a default and expected behaviour—so be it and let’s close this issue. I just thought we could make L5 a little bit friendly.

Yeah, I have wondered if perhaps migrations could be run in a transaction, so that if they fail they can rollback as if nothing ever happened. However, I’m not sure if you can perform schema changes in a transaction or not.

So am I.
And maybe everything could be run in a try-catch.

The whole point of migrate rollback is to be able to manage the database without manual interaction with your DBMS. If a migration has an error, in most cases you should be able to rollback. There have been times, where I’ve made a migration with a syntax error and have had to do manual interactions too, so in the end the system is not 100% perfect. maybe I’ll dive deeper into it later and pose a fix for this.

In this case, without doing too much investigation, I’d recommend the following:
Pull in this/these package(s):
composer require —dev —no-update «xethron/migrations-generator:dev-l5»

composer require —dev —no-update «way/generators:dev-feature/laravel-five-stable»

composer config repositories.repo-name git «git@github.com:jamisonvalenta/Laravel-4-Generators.git»

The packages will allow you to pull in and generate the migration files directly from your database. After pulling in those packages, manually create the table in your database and apply the composite primary key to your relevant columns, or fields. Then run:

php artisan migrate:generate

This will generate the migration files of the tables and columns that already exist in your database with the syntax that is expected by laravel’s migration system for tables with composite primary keys!

See Below Reference for further documentation.
Reference:
https://github.com/Xethron/migrations-generator

@dreferreira sorry, but have you read the entire post?

Yeah was a little late on that, sorry hahaha

See this Issue about Transactions in Migrations:
#302

Right, but we can’t have it both ways? Which is least worst?

I suppose one benefit of not having transactions is that you know what part of the migration failed, and you can predict what state the database has been left in to roll it back manually (all of the migration before the failing line of code).

With transactions part of the migration will be reverted for you but some of it might not be (as Taylor mentioned in the other thread) so you may need to dig deeper to fully manually revert a failed migration.

In this way, I would think that no transactions makes a failure a little more predictable and is «less worse».

@dreferreira Laravel provides a layer to manage some of your DB needs, it doesn’t replace the DBMS.
On any project you will be required to write some SQL and use DBMS, laravel cannot solve everything for you.
Notice that even on the Laravel website on the homestead guide there are links to DBMS.

Perhaps rather than automatically running the down method on failure (as suggested in the other thread) perhaps we could log the migration as having been run at the start rather than the end of the process. So if it fails halfway through it has technically still run and the developer can rollback which will (for most use cases) restore the database to how it should be. At the moment when the migration fails, we have a database half migrated and no log of it. With this change you end up with a database half migrated and a record in the database of the last migration. That would be a big change no doubt.

Or you could wrap this up into a new console method eg ‘migrate:undo’ which gets the next available migration that hasn’t been run (which would likely be the one that caused an exception) and call the down method. A bit convoluted but would not be a breaking change. And only those that find it necessary can use it.

Or have a way of simply triggering the down method of any migration. That way you could rollback without having to manually go into the db.

Just throwing these ideas out there, i’m sure there is something I’m not considering. And the status quo is probably the safest course of action here.

Источник

#1068 — Multiple primary key defined возникает ошибка при импорте

Форум PHP-MyAdmin.RU → Работа с phpMyAdmin → #1068 — Multiple primary key defined возникает ошибка при импорте

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Сообщения 2

1 Тема от servomech 2015-12-10 10:40:31

servomech
Новичок
Неактивен

Зарегистрирован: 2015-12-10
Сообщений: 1

Тема: #1068 — Multiple primary key defined возникает ошибка при импорте

Подскажите, не могу импортировать копию БД
постоянно выдает ошибки, хотя реальная БД работает нормально.

— Индексы таблицы `tgerc_update_sites_extensions`
—
ALTER TABLE `tgerc_update_sites_extensions`
ADD PRIMARY KEY (`update_site_id`,`extension_id`);
Ответ MySQL: Документация

#1068 — Multiple primary key defined
—
и это ошибка при импорте другим способом:

Error displaying the error page: Application Instantiation Error: Table ‘cl70468_smn1.tgerc_session’ doesn’t exist SQL=DELETE FROM `tgerc_session` WHERE `time` 2 Ответ от Hanut 2015-12-10 15:03:05

Hanut
Модератор
Неактивен

Откуда: Рига, Латвия
Зарегистрирован: 2006-07-02
Сообщений: 9,722

Re: #1068 — Multiple primary key defined возникает ошибка при импорте

Поле у первичного ключа может быть только одно. Если нужен индекс по второму полю, то лучше его задать в виде уникального ключа (UNIQUE KEY).

Сообщения 2

Чтобы отправить ответ, вы должны войти или зарегистрироваться

Форум PHP-MyAdmin.RU → Работа с phpMyAdmin → #1068 — Multiple primary key defined возникает ошибка при импорте

Форум работает на PunBB , при поддержке Informer Technologies, Inc

Источник

Syntax error or access violation 1068 multiple primary key defined

public function up()n <n Schema::create(‘post_reviews’, function (Blueprint $table) <n $table->increments(‘id’);n $table->unsignedInteger(‘user_id’);n $table->unsignedInteger(‘post_id’);n $table->mediumText(‘description’);n $table->unsignedInteger(‘rating’);n $table->tinyInteger(‘approved’)->nullable();n $table->timestamps();n $table->primary([‘user_id’, ‘post_id’]);n $table->foreign(‘post_id’)->references(‘id’)->on(‘posts’)->onDelete(‘cascade’);n >);n >n n

The issue I am having is that it sees two primary keys. After some research I discovered that the increments(‘id’) is what is causing the issue. If I remove that column the migration works fine. How could I set this up so I still have the increments(‘id’) and the $table->primary([‘user_id’, ‘post_id’]); ? n

if you have these primary keys n

$table->primary([‘user_id’, ‘post_id’]); n n

so why you need incremental id? n»,»bodyInMarkdown»:»if you have these primary keys rnrnt$table->primary([‘user_id’, ‘post_id’]); rnrnso why you need incremental id?»,»replies»:[<«id»:847679,»conversation_id»:89235,»body»:»

if you have these primary keys n

$table->primary([‘user_id’, ‘post_id’]); n n

Источник

Русские Блоги

ERROR 1068 (42000): Multiple primary key defined

Сообщается об ошибке при изменении положения столбцов в таблице MySQL:

Причина ошибки: первичный ключ определяется дважды;

Решение: удалить первичный ключ: первичный ключ;

mysql> alter table users modify id smallint unsigned first;

Интеллектуальная рекомендация

Реализация оценки приложения iOS

Есть два способа получить оценку приложения: перейти в App Store для оценки и оценка в приложении. 1. Перейдите в App Store, чтобы оценить ps: appid можно запросить в iTunes Connect 2. Встроенная оцен.

JS функциональное программирование (е)

Давайте рассмотрим простой пример, чтобы проиллюстрировать, как используется Reduce. Первый параметр Reduce — это то, что мы принимаем массив arrayOfNums, а второй параметр — функцию. Эта функция прин.

PWN_JarvisOJ_Level1

Nc первый Затем мы смотрим на декомпиляцию ida Перед «Hello, World! N» есть уязвимая_функция, проверьте эту функцию после ввода Видно, что только что появившийся странный адрес является пе.

Установка и развертывание Kubernetes

На самом деле, я опубликовал статью в этом разделе давным -давно, но она не достаточно подробно, и уровень не является ясным. Когда я развернулся сегодня, я увидел его достаточно (хотя это было успешн.

На стороне многопроцессорного сервера — (2) *

Обработка сигнала Родительский процесс часто очень занят, поэтому вы не можете просто вызвать функцию waitpid, чтобы дождаться завершения дочернего процесса. Затем обсудите решение. Обратитесь .

Источник

3685 / 962 / 114

Регистрация: 10.01.2010

Сообщений: 2,550

02.07.2011, 22:28. Показов 27702. Ответов 3

SQL-запрос:

CREATE TABLE `v_sites` (
`ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
`CODE` CHAR( 32 ) KEY ,
`NAME` VARCHAR( 512 )
)

Ответ MySQL:
#1068 — Multiple primary key defined

Где оно увидело здесь два PRIMARY KEY?

Vovan-VE

13207 / 6595 / 1041

Регистрация: 10.01.2008

Сообщений: 15,069

03.07.2011, 06:37

insideone, Он просто KEY после поля считает PRIMATY KEY’ем:

Код

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
      [AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
      [COMMENT 'string'] [reference_definition]
      [COLUMN_FORMAT {FIXED|DYNAMIC|DEFAULT}]
      [STORAGE {DISK|MEMORY|DEFAULT}]

The key attribute PRIMARY KEY can also be specified as just KEY when given in a column definition. This was implemented for compatibility with other database systems.

Если хотел UNIQUE KEY, то либо так:

SQL

CREATE TABLE `v_sites` (
  `ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `CODE` CHAR( 32 ) UNIQUE KEY,
  `NAME` VARCHAR( 512 )
)

либо так

SQL

CREATE TABLE `v_sites` (
  `ID` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  `CODE` CHAR( 32 ),
  `NAME` VARCHAR( 512 ),
  UNIQUE KEY `CODE` (`CODE`)
)

insideone

3685 / 962 / 114

Регистрация: 10.01.2010

Сообщений: 2,550

03.07.2011, 14:02

[ТС]

Сообщение от Vovan-VE

Он просто KEY после поля считает PRIMATY KEY’ем

Вот оно как… Да я в общем-то просто индекс хотел задать. Теперь понял что нужно так:

SQL

1	KEY `CODE` (`CODE`)

0 / 0 / 0

Регистрация: 19.10.2015

Сообщений: 10

23.10.2015, 21:00

А у меня эта ошибка «прошла» сама собой на следующий день. Даже не знала, что так бывает. Вчера искала инфу, как ее решить, но времени не хватило. Сегодня открыла базу — все отличненько! Как и не бывало проблемы.

Источник