Mixing
MySQL - Entries with duplicate unique fields
I've got two entires here in a MySQL database.
Entries:
As you can see these have the exact same slug.
But if we look at the structure.
Structure:
It has the unique constraint. This has been there since the table was created.
Nothing has been changed as well.
I didn't think this was possible. Am I missing something?
EDIT - Testing for whitespace
I actually had another instance this happened here:
Instance 2
Here is the output of select distinct slug from listing
Output
mysql database unique
asked Nov 19 '18 at 15:55
Reece Ward
256
add a comment |
I've got two entires here in a MySQL database.
Entries:
As you can see these have the exact same slug.
But if we look at the structure.
Structure:
It has the unique constraint. This has been there since the table was created.
Nothing has been changed as well.
I didn't think this was possible. Am I missing something?
EDIT - Testing for whitespace
I actually had another instance this happened here:
Instance 2
Here is the output of select distinct slug from listing
Output
mysql database unique
asked Nov 19 '18 at 15:55
Reece Ward
256
1
what is output ofSHOW CREATE TABLE your_table_name?
– Alex
Nov 19 '18 at 15:57
1
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22
add a comment |
I've got two entires here in a MySQL database.
Entries:
As you can see these have the exact same slug.
But if we look at the structure.
Structure:
It has the unique constraint. This has been there since the table was created.
Nothing has been changed as well.
I didn't think this was possible. Am I missing something?
EDIT - Testing for whitespace
I actually had another instance this happened here:
Instance 2
Here is the output of select distinct slug from listing
Output
mysql database unique
asked Nov 19 '18 at 15:55
Reece Ward
256
I've got two entires here in a MySQL database.
Entries:
As you can see these have the exact same slug.
But if we look at the structure.
Structure:
It has the unique constraint. This has been there since the table was created.
Nothing has been changed as well.
I didn't think this was possible. Am I missing something?
EDIT - Testing for whitespace
I actually had another instance this happened here:
Instance 2
Here is the output of select distinct slug from listing
Output
mysql database unique
mysql database unique
asked Nov 19 '18 at 15:55
Reece Ward
256
asked Nov 19 '18 at 15:55
Reece Ward
256
edited Nov 19 '18 at 16:08
asked Nov 19 '18 at 15:55
Reece Ward
256
asked Nov 19 '18 at 15:55
Reece Ward
256
asked Nov 19 '18 at 15:55
Reece Ward
256
256
1
what is output ofSHOW CREATE TABLE your_table_name?
– Alex
Nov 19 '18 at 15:57
1
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22
add a comment |
1
what is output ofSHOW CREATE TABLE your_table_name?
– Alex
Nov 19 '18 at 15:57
1
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22
1
1
what is output of
SHOW CREATE TABLE your_table_name ?– Alex
Nov 19 '18 at 15:57
what is output of
SHOW CREATE TABLE your_table_name ?– Alex
Nov 19 '18 at 15:57
1
1
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22
add a comment |
1 Answer
1
active
oldest
votes
The only explanation I can think of is that those two values in the slug column actually do differ from whitespace. Assuming that leading/trailing whitespace is the culprit, the following query should confirm that:
SELECT DISTINCT slug
FROM yourTable;
If this shows two records with comment-3, then the two slug values are not the same. Now trying trimming both records:
SELECT DISTINCT TRIM(BOTH ' ' FROM slug) slug
FROM yourTable;
If my conjecture be correct, then you should see just a single value now.
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint onslug, then two records can't have the same value.
– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378344%2fmysql-entries-with-duplicate-unique-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
The only explanation I can think of is that those two values in the slug column actually do differ from whitespace. Assuming that leading/trailing whitespace is the culprit, the following query should confirm that:
SELECT DISTINCT slug
FROM yourTable;
If this shows two records with comment-3, then the two slug values are not the same. Now trying trimming both records:
SELECT DISTINCT TRIM(BOTH ' ' FROM slug) slug
FROM yourTable;
If my conjecture be correct, then you should see just a single value now.
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint onslug, then two records can't have the same value.
– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
add a comment |
The only explanation I can think of is that those two values in the slug column actually do differ from whitespace. Assuming that leading/trailing whitespace is the culprit, the following query should confirm that:
SELECT DISTINCT slug
FROM yourTable;
If this shows two records with comment-3, then the two slug values are not the same. Now trying trimming both records:
SELECT DISTINCT TRIM(BOTH ' ' FROM slug) slug
FROM yourTable;
If my conjecture be correct, then you should see just a single value now.
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint onslug, then two records can't have the same value.
– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
add a comment |
The only explanation I can think of is that those two values in the slug column actually do differ from whitespace. Assuming that leading/trailing whitespace is the culprit, the following query should confirm that:
SELECT DISTINCT slug
FROM yourTable;
If this shows two records with comment-3, then the two slug values are not the same. Now trying trimming both records:
SELECT DISTINCT TRIM(BOTH ' ' FROM slug) slug
FROM yourTable;
If my conjecture be correct, then you should see just a single value now.
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
The only explanation I can think of is that those two values in the slug column actually do differ from whitespace. Assuming that leading/trailing whitespace is the culprit, the following query should confirm that:
SELECT DISTINCT slug
FROM yourTable;
If this shows two records with comment-3, then the two slug values are not the same. Now trying trimming both records:
SELECT DISTINCT TRIM(BOTH ' ' FROM slug) slug
FROM yourTable;
If my conjecture be correct, then you should see just a single value now.
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
answered Nov 19 '18 at 15:59
Tim Biegeleisen
218k1387140
218k1387140
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint onslug, then two records can't have the same value.
– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
add a comment |
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint onslug, then two records can't have the same value.
– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
It only shows one record with it, i'll update my post with this information
– Reece Ward
Nov 19 '18 at 16:05
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
What does "It" refer to here? My first query or my second query?
– Tim Biegeleisen
Nov 19 '18 at 16:06
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
Sorry, first query!
– Reece Ward
Nov 19 '18 at 16:09
What you are describing is not possible. If you have a unique constraint on
slug, then two records can't have the same value.– Tim Biegeleisen
Nov 19 '18 at 16:11
What you are describing is not possible. If you have a unique constraint on
slug, then two records can't have the same value.– Tim Biegeleisen
Nov 19 '18 at 16:11
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
I have everything in front of me, is there anything else I can show you?
– Reece Ward
Nov 19 '18 at 16:14
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53378344%2fmysql-entries-with-duplicate-unique-fields%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
what is output of
SHOW CREATE TABLE your_table_name?– Alex
Nov 19 '18 at 15:57
1
Can we see the ouput of HEX(slug)
– Strawberry
Nov 19 '18 at 16:17
@Strawberry look at the comments on the answer below, we tested for distinctness
– Reece Ward
Nov 19 '18 at 16:21
I still think SHOW CREATE TABLE might be useful
– Strawberry
Nov 19 '18 at 16:22