postgresql – Should I drop a btree index on a column after adding a unique constraint?

On one of my existing tables I have a btree index. I am now adding a unique constraint to this column. I know behind the scenes it will add a UNIQUE btree index to this column as well.

In this scenario, should I drop the original btree index added to the column to prevent redundant indexes?

Thanks! :]

database – Integrity constraint violation 1062 ALTER TABLE on setup:upgrade

Magento ver. 2.3.5
mariadb 10.2
PHP 7.2

We are hitting the constraint violation error below when running: php bin/magento setup:upgrade
catalog_product_entity_varchar ran out of space at 2147483647 so we increased the value_id int to Bigint.
We are able to edit and add products again. But now have the constraint error coming up when it reaches schema / update.
What is the best way to correct this, can we change the constraint rule?

SQLSTATE(23000): Integrity constraint violation:
1062 ALTER TABLE causes auto_increment resequencing,
resulting in duplicate entry ‘2147483647’ for key ‘PRIMARY’,
query was: ALTER TABLE catalog_product_entity_varchar MODIFY COLUMN
value_id int(11) NOT NULL AUTO_INCREMENT COMMENT “Value ID”

Thanks so much.
Tony

complexity theory – Does having a similar constraint while reducing a problem to similar problem to prove np hard means they are same?

I have been trying to find the computational complexity of my optimization problem and found that it is Np-Hard. To prove it to Np-Hard, I try reducing it Nurse Scheduling Problem. I am quite confused with the idea during the reduction. Does having a similar constraint in my problem and NSP means that both problems are the same and have a solution already?

My problem:
We have a set of classes, and each class has a set of timing associated with a penalty. We have hard and soft constraints where every soft constraint violation. We have to schedule classes to timing such that the sum of all penalties is reduced.

We just have a hard constraint stating: Two classes cannot be scheduled at the same time of day and week (overlap constraints).

Here is the link to the nurse scheduling problem: Nurse Scheduling problem and they have a constraint stating: Two nurses dislike each other and thus cannot work on the same shift because of that.

I have to question:

Does this make my problem the same as the NSP problem?

Does my approach of reduction to NP-HARD is wrong? If yes, where did I go wrong? Is there any similar problem that I can use for reduction?

Can someone please help me with this?

elimination theory – Growth in constraint matrix sizes by eliminating variables in integer programming

Let $underline{x},underline{y}$ and $underline{z}$ be three sets of integer variables and let the relations be

$$A[underline{x},underline{y}]’leq b$$
$$B[underline{y},underline{z}]’leq c.$$

Rewriting in terms of $underline{y}$ we get

$$A_{mbox{left}}underline{x}+b_{mbox{left}}lequnderline{y}leq A_{mbox{right}}underline{x}+b_{mbox{right}}$$

$$B_{mbox{left}}underline{z}+c_{mbox{left}}lequnderline{y}leq B_{mbox{right}}underline{z}+c_{mbox{right}}.$$

Is it right to state the relations between $underline{x}$ and $underline{z}$ are

$$A_{mbox{left}}underline{x}+b_{mbox{left}}leq A_{mbox{right}}underline{x}+b_{mbox{right}}$$

$$B_{mbox{left}}underline{z}+c_{mbox{left}}leq B_{mbox{right}}underline{z}+c_{mbox{right}}?$$

And so by eliminating $underline{y}$ we get

$$C[underline{x},underline{z}]leq d$$

where the number of rows is $C$ is bound above in terms of the product of number of rows in $A$ and $B$.

Is it correct eliminating common variables grows the number of variables multiplicatively?

Get original entity in a constraint

Is it possible to get the original entity in a constraint?

Use case: create a constraint on which item can be deleted in a multi value field

postgresql – GIST exclude constraint with a specific value

Is there a way to represent a constraint, where i prevent date range overlaps, but only when a column equals a specific value? So i can have zero or many inactive, but only ever one active?

Im running into a problem where i need a gist constraint to check date range overlaps but only if a column equals a specific value (‘active’ in this case).
We have a table which has dates and there can be overlap, if a column equals a specific value (active can only ever have one, but inactive can be zero or many).

GIST seems to be able to say prevent any duplicate (2 active, but also excludes 1 active, 1 inactive, or inactive > 1).

Putting this in two constraints fails, because the gist only covers the range overlap and join id.

e.g.
ALTER TABLE "a_table" ADD CONSTRAINT "CHK_some_name" EXCLUDE USING gist ("a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

ALTER TABLE "a_table" ADD CONSTRAINT "CHK_some_name" EXCLUDE USING gist ("an_enum" with =, "a_join_id" WITH =, tstzrange("start_time", "end_time") WITH &&)

Any help/insight would be greatly appreciated

sql server – Best way to copy tables without a primary key or unique constraint?

Looking into synchronizing tables in a database across multiple servers using a DML method like MERGE, only to realize that some of the largest tables that take up the majority of the total size of the database do not have a primary key or unique constraint and therefore have duplicate data. As a result, doing a MERGE results in a full table scan, which explodes tempdb. For business reasons, I cannot alter the table structure or data itself.

Is there a way to copy these tables on a routine basis other than backing up and restoring the entire database?

postgresql – Setting UNIQUE constraint on TIMESTAMP (WITH TIME ZONE)

I want to scrape a table from a website and synchronize the data into my own database i.o. to detect whenever there is a new entry available. The table has a timestamp column which tells me when the new record was created.

However, I have different tables for multiple objects so in order to know if there is a new entry, I’d need a unique-constraint on (object_id, timestamp):

object_id  | timestamp           | value
------------------------------------------
1          | 2021-01-01 00:00:00 | 100      <--|
1          | 2021-01-02 00:00:00 | 101         | error: have an
1          | 2021-01-03 00:00:00 | 102         | equal timestamp
1          | 2021-01-01 00:00:00 | 103      <--| 
2          | 2021-01-10 00:00:00 | 1002
2          | 2021-01-12 00:00:00 | 1003

It feels a bit odd to have such a unique-constraint so I was wondering if this is the only (or best) way I could do this?

constraint satisfaction – QuickSort when the range of data is known

In QuickSort Algorithm, the pivot is chosen as the first element or a randomised element. However, if the range of data to be sorted is known, For example, from 1 to 100, and they are mostly equally distributed, then, the best choice for first pivot would be 50, even if it’s not present in the array, then, for left sub-array the best pivot would be 25 and for right sub-array it would be 75, and so on.

Is there a better sorting algorithm for such distributed data in a given range? Will such QuickSort be more efficient than any other Algorithm for such data?

regular languages – Why does the Pumping Lemma Constraint |xy| ≤ p mean that y can’t be 1 in the string 0p1p

I am trying to get my head around the Pumping Lemma to prove a language is non-regular.

I am reading the Sipser text book and he gives the following example.

Let B be the language ${0^n 1^n | n ge 0}$

Let $s = 0^p 1^p$

I understand that the idea is you can split this string into xyz such that y can be pumped. It is the constraint of $|xy| le p$ that is confusing me.

Sipser notes that due to this constraint y could not equal 01 nor could it equal 1. Why would y equaling either of those values violate the given constraint.

I am generally quite confused by the Pumping Lemma so any general advice or good resources you can recommend, I would appreciate.

Thanks!