Make the primary key duplicable

0

When I insert the data into my SQL table everything is normal.

However, when trying to insert a second time, with the same value in id that 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 ;
    
asked by anonymous 09.04.2015 / 22:39

2 answers

3

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

What you can do is make the primary key unique with something other than id . You have not given much information on how your table is but could have the primary key id+item , for example. Then, even though the id is 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 table definition.

But think carefully, because id should be unique, if it can not be unique, you should rethink it. You are probably using it improperly, it is not working as an identifier, which is supposed to be unique.

But since you have a column that looks every bit as unique, after all you've even put up a AUTO_INCREMENT in it, your problem must be another one. You do not want to put anything duplicate. You just want to let the system create id for you.

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

If you really need this char_id to be repeated, then create a new column to do this role, and in that column you only go with AUTO_INCREMENT , it will set it PRIMARY KEY and at insertion time will use DEFAULT as the insertion value.

    
09.04.2015 / 22:47
4

One of the requirements of Primary key is to not allow duplicates. Its function is to identify each of the records (rows) of the table. So you have to have unique and non-zero values.

If you need to have this column with repeated values, it can not be Primary key .

To avoid situations like this, it is recommended that the Primary key be a column independent of the data and managed by the bank. Its value is automatically assigned by the bank engine.

In MySQL the way to declare it is:

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

EDIT - Following the comments exchanged in the question.

If you need to assign values to the char_id column, do not set it as the primary key. However it is recommended that the table has one. Here's how:

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 char_id column becomes a normal column and the table will have the primary key in the id column.

    
09.04.2015 / 22:48