[SOLVED] Duplicate Entry Key error when initial 6 characters match

Duplicate Entry Key error when initial 6 characters match

I have a table BLE_PINS with columns TN, MAC, PIN, and Date. MAC is the primary key. When entering

INSERT INTO `BLE_PINS` (`MAC`, `PIN`) VALUES ('SEEP413E68','99933');

I get the error

#1062 - Duplicate entry 'SEEP41' for key 'PRIMARY'`.

It seems to be matching only the first 6 characters. Is there a way to change that?

Table definition:

CREATE TABLE BLE_PINS ( 
    TN varchar(16) DEFAULT NULL, 
    MAC varchar(100) NOT NULL, 
    PIN varchar(6) NOT NULL, 
    Date timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, 
    PRIMARY KEY (MAC(6)) 
) ENGINE=InnoDB DEFAULT CHARSET=latin1

Solution

PRIMARY KEY (MAC(6))

means that only the first 6 characters of MAC should be used as the primary key. This syntax is documented in Index Prefixes

With col_name(N) syntax in an index specification for a string column, you can create an index that uses only the first N characters of the column.

If you want the entire column to be the key, use

PRIMARY KEY (MAC)