## postgresql ltree – how to write a check constraint to guarantee ancestors?

Since this is a tree structure, you can use a “parent” field with a FOREIGN KEY reference to the table itself, as described in this article.

For example, if you had another item `E` with `tree_path` = `3.1`, then the `parent` values of `B, C, D` would be `E`. The parent of `E` would be `A`. The parent of `A` would be `NULL`.

If you delete `E`, then enforcing the foreign key constraint would require a cascaded deletion of the children `B, C, D`.

## insert – Constraint in ON CONFLICT clause has no associated index

I have this simple setup

``````    CREATE TABLE stop (
id INT UNIQUE,
name TEXT,
PRIMARY KEY(id)
);

CREATE TABLE line (
id INT UNIQUE,
name TEXT,
PRIMARY KEY(id)
);

CREATE TABLE line_stops_sequence(
id SERIAL PRIMARY KEY,
line INT,
stop INT,
CONSTRAINT fk_line FOREIGN KEY(line) REFERENCES line(id),
CONSTRAINT fk_stop FOREIGN KEY(stop) REFERENCES stop(id)
);
``````

When I launch the following sentence

``````INSERT INTO line(id, name) VALUES (1, 'Line 1');
INSERT INTO stop(id, name) VALUES (2, 'Stop 2');
INSERT INTO line_stops_sequence(line, stop) VALUES (1,1), (1,2) ON CONFLICT ON CONSTRAINT fk_stop DO NOTHING;
``````

I get `constraint in ON CONFLICT clause has no associated index`;

I know I’m inserting an stop id that is not included in the `stop` table. The idea is that I want to ignore the `INSERT`s that have conflicts. In the example it is just two items, but I’m inserting bulk data.

How can I do it without having to manually check the result of the query? Is there anyway to do it with the SQL sentence?

## oc.optimization and control – Minimize expression under constraint, that solution must be positive

I want to calculate $$vec{x}$$ such that the expression
$$lVerthat{A} vec{x} – vec{B}rVert²$$
shall be minimized under the constraint, that each component $$x_i$$ of the vector $$vec{x}$$ shall be positive.

I tried it with Lagrange-multipliers, but I didn’t know how to formulate the constraint, since I only came up with $$x_i = vert x_ivert$$, which isn’t a function $$mathbb{R}^n to mathbb{R}$$, which I would need (as far as I understood).

It would be great, if you could help me.

## magento2 – Importing existing products: Integrity constraint violation: 1062 Duplicate Entry

I have been importing existing products to a local environment through the admin dashboard. I realized that many of the products I imported didn’t include their attributes, so I went back to make sure they were included by changing the ‘Multiple value separator’ to a pipe. Now though, when I import I get the following error:

``````General system exception happened
SQLSTATE(23000): Integrity constraint violation: 1062 Duplicate entry 'sauces-rubs/healthy-choices/gluten-free-default-category/healthy' for key 'url_rewrite.URL_REWRITE_REQUEST_PATH_STORE_ID', query was: INSERT INTO `url_rewrite` (`redirect_type`,`is_autogenerated`,`metadata`,`description`,`store_id`,`entity_type`,`entity_id`,`request_path`,`target_path`) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
``````

I have deleted all products and categories, I have wiped the url_rewrites table clean, and I have attempted to regenerate them using this module. What am I missing? I am new to Magento, so please let me know if I need to include more information, but this is all that I am aware of at the moment.

## mysql – Can’t create a table check constraint

As the error message states, you have an issue with the check constraint having an upper limit check, which will cause problems with an `AUTO_INCREMENT` option enabled. If this table must have an `id` value of 0, 1, or 2, and you do not want to manually specify it at the time of record insertion, then you will need to use a `BEFORE INSERT` trigger to determine which `id` value is available and assign it.

## mysql – Error Code: 1215. Cannot add foreign key constraint

I’m creating a DBSM on Mysql Workbench, but when Itry creating the last table i get an error because it can’t add the foreign key, but i can’t understand why since the child key has the same data type and collation as the parent key and the name isn’t repeated.

That’s the code for the two tables:

``````CREATE TABLE PRESTITO(
MATRICOLA_U INT,
CODICE_LIB SMALLINT(4),
DATA_P DATE,
DATA_R DATE,
N_DIP SMALLINT(2),

PRIMARY KEY(CODICE_LIB),

FOREIGN KEY(MATRICOLA_U) REFERENCES UTENTI(MATRICOLA),
FOREIGN KEY(CODICE_LIB) REFERENCES LIBRI(CODICE_L)
);

CREATE TABLE DIPARTIMENTO(
NUMERO_DIP SMALLINT(2),
NOME_DIP VARCHAR(50) NOT NULL,
INDIRIZZO_DIP VARCHAR(50),

PRIMARY KEY(NUMERO_DIP),
FOREIGN KEY (NUMERO_DIP) REFERENCES PRESTITO(N_DIP)
);
``````

Is there someone able to help?
Thank you very much!

## Solve equation with constraint that some variables can’t depend on other variables

Take the equation $$f(x,y,z) = x y^2 + (1-x)(y-z)^2$$. I want to rewrite this in the form $$(y+a(x,z))^2 + b(x,z)$$. This can easily be solved by hand, with $$a(x,z) = – z(1-x)$$ and $$b(x,z) = x(1-x)z^2$$. The actual problem I want to solve is more complicated than this and can’t as easily be done by hand, but the essence of the problem should be the same – I want Mathematica to solve the equation

$$x y^2 + (1-x)(y-z)^2 = (y+a)^2 + b$$

for $$a$$ and $$b$$, with the constraint that $$a$$ and $$b$$ can only be functions of $$x$$ and $$z$$. Is there any way of imposing this condition? Simply plugging in the equation into `Solve`,

``````Solve(x*y^2 + (1 - x) ((y + z)^2) == (y + a)^2 + b, {a, b})
``````

obviously does not work, since the equation by itself is not sufficiently constrained. I tried replacing `a` with `a(x,z)` and likewise for `b`, but Mathematica didn’t know what to do with that and just treated `a(x,z)` as a variable.

## Use regexp in check constraint MySQL

I want to add a check constraint on the primary key of the `accomodatie` table named `code`. This code has to start with 4 letters and end with 4 numbers (Abcd0123). I thought I could use a regexp for that in a check constraint like below:

``````alter table accomodatie add constraint check (
accomodatie.code regexp '/^(A-Z)(a-z){3}d{4}\$/'
);
``````

But when I want to execute the statement above I get an error:

``````Error Code: 3819. Check constraint 'accomodatie_chk_1' is violated.
``````

I have also tried `like` instead of `regexp` but that does not do the trick.

## Adding constraint to existing drupal commerce order item type field

I want to add a constraint to an already existing order item type field but the field settings say “There is data for this field in the database. The field settings can no longer be changed.”.

Currently the max. field length is 255 chars. I want to limit it to 100 chars and issue an error in the form if that number is exceeded. I would be happy if I could just control what’s submitted through the form without necessarily changing anything in the database.

Is there a way to do this?

Thanks,
P

## php – Laravel 8: SQLSTATE[23000]: Integrity constraint violation: 1452 Cannot add or update a child row: a foreign key constraint fails

I want to apply OneToMany Relationship between `users` and `roles` table and I have already some data in these two tables.

So in order to make this relationship, I made this Migration for adding `role_id` to `users` table:

``````public function up()
{
Schema::table('users', function (Blueprint \$table) {
\$table->unsignedBigInteger('role_id')->nullable()->unsigned();
});
}
``````

But when I want to run this Migration, I get this error:

SQLSTATE(42S21): Column already exists: 1060 Duplicate column name
‘role_id’ (SQL: alter table `users` add `role_id` bigint unsigned
null)

So what is going wrong here ? How can I fix this issue ?

Here is also the Migration of `roles` table:

``````public function up()
{
Schema::create('roles', function (Blueprint \$table) {
\$table->id();
\$table->string('name');
\$table->string('slug');
\$table->timestamps();
});
}
``````