Make primary key uplinkable

Asked

Viewed 864 times

0

When entering the data in my SQL table everything happens normal.

However, when trying to insert for the second time, with the same value in id which is the main field of the table, it returns the following error: Duplcate entry '156' for key 'PRIMARY'.

However, I want my system to insert a new data line that has the same ID. How do I make this Primary key 'duplicate' ?

SQL:

CREATE TABLE IF NOT EXISTS `controledegm` (
  `char_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `char_name` varchar(255) NOT NULL,
  `item_id` int(11) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_quantidade` int(11) NOT NULL,
  `data` varchar(20) DEFAULT NULL,
  `hora` varchar(20) NOT NULL,
  PRIMARY KEY (`char_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=150007 ;
mysql sql

  • In this case you don’t need Primary Key, the correct thing is you create the table without setting it that everything will go well.

  • And how I create without defining her?

  • Post the sql you used to create the table.

  • I edited in the content of the question

  • You already have the primary key as AUTO_INCREMENT. In Insert you should not assign value to it. Post Insert/Update giving the error.

  • @ramaral ta but in case just remove this AUTO_INCREMENT... In my case the only problem is that all my columns may come to repeat their value, and the key Primary cannot be repeated, so the question is whether there is a way to create a table without this primary key or should I create a column only to store this primary key...

  • I edited my answer.

  • @Ggirotto: by definition a primary key is a field whose value uniquely identifies each row of a table, so it cannot exist in its table two rows with the same value for primary key.

Show 3 more comments

3 answers

5

One of the requirements of Primary key is not to allow duplicates.
Its function is to identify each of the records(rows) in the table.
So it has to have unique values and not null values.

If it is your need to have this column with repeated values it cannot be Primary key.

To avoid situations like this it is recommended that the Primary key is an independent data column and managed by the database.
Its value is automatically assigned by the seat engine.

In the Mysql how to declare it: 

CREATE TABLE Persons
(
    ID int NOT NULL AUTO_INCREMENT,
    .....
    .....
    PRIMARY KEY (ID)
 )

EDIT - Following the mixed comments on the question.

If you need to assign values to the column char_id do not set it as the primary key.
However it is recommended that the table has a.
Do so:

CREATE TABLE IF NOT EXISTS `controledegm` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `char_id` int(11) unsigned,
  `char_name` varchar(255) NOT NULL,
  `item_id` int(11) NOT NULL,
  `item_name` varchar(255) NOT NULL,
  `item_quantidade` int(11) NOT NULL,
  `data` varchar(20) DEFAULT NULL,
  `hora` varchar(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=150007 ;

The column char_id becomes a normal column and the table will have the primary key in the column id.

3


This is not possible so directly. The primary key cannot contain duplicates. Depending on the database system may not even be in any order, but do not think that is your case, even if you do not inform which one is using.

What you can do is make the primary key unique with something other than id. You didn’t give much information on how your table is but could have the primary key id+item, for example. There, even if the id be equal, the column item being different would make the key unique.

In Mysql this is done with PRIMARY KEY (char_id, item_id) in the definition of the table.

But think about it, because id should be unique, if it can’t be unique, you should rethink it. Probably using it inappropriately, not functioning as an identifier, which is assumed to be unique.

But since you have a column that looks like one, after all you even put one AUTO_INCREMENT in it, your problem must be another. You do not want to put anything duplicated. You just want to let the system create the id for you.

INSERT INTO controledegm (char_id, char_name, etc) VALUES (DEFAULT, 'nome', etc)

I put in the Github for future reference.

If you really need this char_id if repeated, then create a new column to make this role, and only in this column you will put the AUTO_INCREMENT, will define who she is PRIMARY KEY and at the time of inserting will use the DEFAULT as an insertion value.

  • And you can create a table without setting a key Primary?

  • Yes and no. It is possible not to define, but rarely you should do this. At the bottom the table will have a primary key that you have no control over. And I don’t know if it would solve everything you want. It might work at first and then you realize it was a mistake.

  • So it would be interesting to create a column just to store this key Primary? Because in case all columns of this my table may repeat its value

  • 1

    Yes, it is highly interesting, even if it is only for this. But from what I see you have already done it. The char_id isn’t it already so? With the AUTO_INCREMENT in thesis it will not repeat, unless you try manually, which is not desirable too.

  • Understood. I will do the same then. Thank you so much for the support. I will mark uses reply as the best due to comments. Hug

-1

It is impossible to accomplish this a primary key cannot repeat itself, just remove the AUTO_INCREMENT property from the column

Browser other questions tagged mysql sql

You are not signed in. Login or sign up in order to post.