• Guest, The rules for the P & N subforum have been updated to prohibit "ad hominem" or personal attacks against other posters. See the full details in the post "Politics and News Rules & Guidelines."

SQL allow only one duplicate value in a column

marcUK2

Member
Sep 23, 2019
69
35
51
So a google on this doesnt turn up the answer, I've never had to do this before, but a situation arises where I need a column that is similar to UNIQUE, but I need to allow only 1 duplicate entry

Simplified....For instance I have a product that has a storage location, recorded in a column of

A1
A2
A3
A4

B1
B2
....etc
...etc
Z1

Now, under most circumstances, only one product fits into a storage spot, so I could have this column as unique so that a storage spot is not accidentally assigned twice
But I wonder if I had a half-size product, that could share a storage spot...
So I need to allow up to 2 items in each location, which means UNIQUE is out,
Lets say both items can be put at location A3, but I need to reject any additional attempts to enter a third product at A3

Thanks for help
 

marcUK2

Member
Sep 23, 2019
69
35
51
Im using MySQL Workbench.
I have created this, which outputs the correct boolean depending on the quantity of A3's found, but how do I turn this into an insert using IF ?

SET @resval := (SELECT COUNT(store.location) FROM store WHERE store.location = 'A3');

SELECT IF(@resval = 2, "TRUE", "FALSE");
 

linkgoron

Platinum Member
Mar 9, 2005
2,025
427
136
If I'm understanding you correctly - what you're asking for is some usage of row_number/rank/etc. and partition-by.

https://stackoverflow.com/a/14297055/624596 (just need to add a where-clause)

 

marcUK2

Member
Sep 23, 2019
69
35
51
If I'm understanding you correctly - what you're asking for is some usage of row_number/rank/etc. and partition-by.

https://stackoverflow.com/a/14297055/624596 (just need to add a where-clause)


I don't think that's anything I'm trying to do, but that looks like one very useful website. Thanks
 

Ken g6

Programming Moderator, Elite Member
Moderator
Dec 11, 1999
15,302
2,302
55
This feels like the wrong approach to the problem. Ideas:

1. You could subdivide your storage locations. A3-A and A3-B, for instance. You might like('A3%') this or you might not. ;)
2. You could have storage location and occupancy. For argument's sake, let's say occupancy is an integer percentage. Then you just need to make sure sum(occupancy) grouped by location <= 100%.
 

linkgoron

Platinum Member
Mar 9, 2005
2,025
427
136
I don't think that's anything I'm trying to do, but that looks like one very useful website. Thanks
Something like this could work, IMO:
select * from
(
select *, ROW_NUMBER() OVER(PARTITION BY location ORDER BY id) AS row
from items
) as a
where row <= 2

Column names and table names need to be replaced, but this will give you two rows (at most) for every storage spot (which I called location).

However, I agree with Ken that this seems like the wrong approach, and is definitely not scalable once you can have 3 or different combinations of sizes.
 
  • Like
Reactions: Ken g6

Cogman

Lifer
Sep 19, 2000
10,267
103
106
Here's my suggestion, use a unique constraint and add an id column.

Foo,
Bar,
Version
UNIQUE(Foo, Bar, Version)

Insertion would involve looking up the prior version to increment the next version with a hard cap on the number of copies you want. (So, if you want 2 copies, 1 if not exists and 2 otherwise).

After that, the unique constraint will do it's job and reject insertions of more than X duplicate.

Bonus, this should have some pretty good indexing capabilities.
 

sdifox

No Lifer
Sep 30, 2005
82,590
8,249
126
I am confused, shouldn't location be an entity, thus in a separate table? Then all you have to do is have it in a column in your product table?
 
  • Like
Reactions: sao123 and mxnerd

sao123

Lifer
May 27, 2002
12,571
112
106
This definately feels like non-normalized data...

Item should be in one table, item sizes should be in another, locations in a third, and a link table combining all 3 with quantities, etc.
 
Last edited:

marcUK2

Member
Sep 23, 2019
69
35
51
This definately feels like non-normalized data...

Item should be in one table, item sizes should be in another, locations in a third, and a link table combining all 3 with quantities, etc.
which is what it is, so if I want to limit my 'link' table to only allow a maximum of 2 items in any location - i cant use unique on the foreign key because that is only singular. I need 2
 

sao123

Lifer
May 27, 2002
12,571
112
106
which is what it is, so if I want to limit my 'link' table to only allow a maximum of 2 items in any location - i cant use unique on the foreign key because that is only singular. I need 2
I think you are confusing business logic with data constraints.
This is for your insert algorithm to accomplish, not your table constraints. Why?
(In most production data, data is never actually deleted, but is marked with a datetime value in a 'date deleted' column, in order to maintain complete history of data...

So when you insert an item at storage spot 2b, then remove it and spot 2b is vacant again the next item in storage spot can be inserted in that same spot again... but both records can be maintained for historical purposes... except the previous ones (every item ever stored in that location) will all have DateDeleted = (somedate), ones currently in the spot will have DateDeleted = NULL
Your "only 2 will ever exist" constraint is not valid...

//find any empty holes
Select ProductID, SizeID, Count(LocationID)
Where product = @CurrentProduct and SizeID = @1/2SizeID and Count(LocationID) == 1 and DateDeleted IS NULL

if (results returned == 0) start next available new location.
if (results returned == 1) insert at 2nd half of existing available 1/2 size LocationID
 
Last edited:
  • Like
Reactions: mxnerd and Ken g6

ASK THE COMMUNITY