SQL allow only one duplicate value in a column

[marcUK2]

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]

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]

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)

MySQL ROW_NUMBER and Its Useful Applications

In this tutorial, you will learn about the MySQL ROW_NUMBER() function and how to use it to generate a unique number for each row in the result set.

www.mysqltutorial.org

A Guide to MySQL RANK Funtion By Practical Examples

This tutorial introduces to the MySQL RANK function and how to apply it to assign the rank to each row within the partition of a result set.

www.mysqltutorial.org

 

[marcUK2]

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)

MySQL ROW_NUMBER and Its Useful Applications

In this tutorial, you will learn about the MySQL ROW_NUMBER() function and how to use it to generate a unique number for each row in the result set.

www.mysqltutorial.org

A Guide to MySQL RANK Funtion By Practical Examples

This tutorial introduces to the MySQL RANK function and how to apply it to assign the rank to each row within the partition of a result set.

www.mysqltutorial.org

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

 

[Ken g6]

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]

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.

 

[Cogman]

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]

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?

 

[sao123]

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.

 

[marcUK2]

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]

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