MySQL: Enforce an unique column without using an unique key

Question

I have a column with data that exceeds MySQL's index length limit. Therefore, I can't use an unique key.

There's a solution here to the problem without using an unique key: MySQL: Insert record if not exists in table

However, in the comments, people are having issues with inserting the same value into multiple columns. In my case, a lot of my values are 0, so I'll get duplicate values very often.

I'm using Node and node-mysql to access the database. I'm thinking I can have a variable that keeps track of all values that are currently being inserted. Before inserting, I check if the value is currently being inserting. If so, I'll wait until it finishes inserting, then continue execution as if the value was originally inserted. However, I feel like this will be very error prone.

Here's part of my table schema:

CREATE TABLE `links` (
  `id` int(10) UNSIGNED NOT NULL,
  `url` varchar(2083) CHARACTER SET latin1 COLLATE latin1_general_cs NOT NULL,
  `likes` int(10) UNSIGNED NOT NULL,
  `tweets` int(10) UNSIGNED NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

ALTER TABLE `links`
  ADD PRIMARY KEY (`id`),
  ADD KEY `url` (`url`(50));

I cannot put an unique key on url because it can be 2083 bytes, which is over MySQL's key size limit. likes and tweets will often be 0, so the linked solution will not work.

Is there another possible solution?

You could use a `BEFORE INSERT` trigger that checks whether the URL already exists, and signals an error. — Barmar, Jan 23 '17 at 07:36

score 1 · Accepted Answer · answered Jan 23 '17 at 07:38

If you phrase your INSERT in a certain way, you can make use of WHERE NOT EXISTS to check first if the URL does not exist before completing the insert:

INSERT INTO links (`url`, `likes`, `tweets`) 
SELECT 'http://www.google.com', 10, 15 FROM DUAL
WHERE NOT EXISTS 
(SELECT 1 FROM links WHERE url='http://www.google.com');

This assumes that the id column is a primary key/auto increment, and MySQL will automatically assign a value to it.

MySQL: Enforce an unique column without using an unique key

1 Answers1