Question 20754 in Mysql Question posted in
The official MySQL documentation can be found here.

Why are there are two rows in MariaDB database violating unique constraint? – Mysql

I have written an application in Javascript which inserts data into two tables via a connection to a MariaDB server.

There should be a 1:1 correspondance between the rows in these tables when first running the application.

One table stores (simulated) data about properties, the other table stores data about prices. There should be 1 price for each property. At a later date, the price might change, so there could be more than one entry for the price, but this cannot happen when the application is first run. These entries also cannot be in violation of a unique index – but they are.

Perhaps I have misconfigured something in MariaDB? Here is the code which generates the tables.

drop table if exists property_price;
drop table if exists property;

create table property
(
  unique_id bigint unsigned not null auto_increment primary key,
  web_id bigint unsigned not null,
  url varchar(256),
  street_address varchar(256),
  address_country varchar(64),
  property_type varchar(64),
  num_bedrooms int,
  num_bathrooms int,
  created_datetime datetime not null,
  modified_datetime datetime not null
);

create table property_price
(
  property_unique_id bigint unsigned not null,
  price_value decimal(19,2) not null,
  price_currency varchar(64) not null,
  price_qualifier varchar(64),
  added_reduced_ind varchar(64),
  added_reduced_date date,
  created_datetime datetime not null
);

alter table property_price
add constraint fk_property_unique_id foreign key(property_unique_id)
references property(unique_id);

alter table property
add constraint ui_property_web_id
unique (web_id);

alter table property
add constraint ui_url
unique (url);

alter table property_price
add constraint ui_property_price
unique (property_unique_id, price_value, price_currency, price_qualifier, added_reduced_ind, added_reduced_date);

Below is a screenshot from DBeaver showing that a select statement returns two identical rows.

I don’t understand why the unique constraint appears to be violated. The constraint does sometimes work, because if I run my application again, it fails because it attempts to insert a duplicate row which already exists in the DB. (Not the same as the one shown below.)

Can anyone point me in the right direction as to how I might debug this?

DBeaver

Tags:

2

Answers


  1. Chosen as BEST ANSWER
    0

    MariaDB permits multiple values on columns which form part of a unique constraint.

    My solution would be to put the logic for checking for duplicate rows into the application, rather than this being on the database side. Essentially this means the unique constraint is not being used.

    An alternative to this might be to restructure the design of the database tables, for example by factoring out the columns which can be null into a different table.

    In my case this is not possible, because one row represents the data that was obtained at a particular point in time. Therefore, it is the case that the design of MariaDB does not lend itself well to storing data for my particular use case. There is nothing that can be done about this, and therefore additional logic is required in the application. No amount of down votes will change this reality.

    If MariaDB had a setting to cause prevent multiple null values from being valid within a row where a unique index is defined on nullable columns, then things would be different.

    There is another possible alternative, but it isn't much better.

    By making the columns not null, the unique index problem goes away, and we cannot have duplicate rows. However, if we have data to insert where some values are null, then additional logic is again required on the application side to insert a dummy value to represent null.

    This could perhaps be an empty string, but this might be confusing, so a special string such as <NULL> might be preferable.

    However, this does not avoid the problem of requiring additional logic on the application side. We would now need logic for both insertion and data retrival (select), which checks for <NULL>, and converts this to some nullable type for whichever language is being used to interface with the database.


  2. 0

    NULL values ​​in unique indexes do not work. price_qualifier is NULL in both records. Therefore, duplicate records become possible.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search