1

I have this table called Hierarchies. This is its actual definition:

CREATE TABLE `Hierarchies` (
  `Id` bigint(20) NOT NULL AUTO_INCREMENT,
  `Guid` uuid NOT NULL DEFAULT uuid(),
  `Key` varchar(400) NOT NULL DEFAULT uuid(),
  `Slug` varchar(400) NOT NULL DEFAULT uuid(),
  `Order` bigint(20) NOT NULL DEFAULT 0,
  `EntityTypeGuid` uuid NOT NULL,
  `Title` varchar(400) NOT NULL,
  `ImageGuid` uuid DEFAULT NULL,
  `IconGuid` uuid DEFAULT NULL,
  `IconSvg` varchar(400) DEFAULT NULL,
  `ParentId` bigint(20) DEFAULT NULL,
  `Description` varchar(400) DEFAULT NULL,
  `IsActive` bit(1) DEFAULT NULL,
  `ItemsCount` int(11) DEFAULT NULL,
  `Level` int(11) DEFAULT NULL,
  `IsLeaf` bit(1) DEFAULT NULL,
  PRIMARY KEY (`Id`),
  UNIQUE KEY `IX_Hierarchies_Unique_Guid` (`Guid`),
  UNIQUE KEY `IX_Hierarchies_Unique_Key` (`Key`),
  UNIQUE KEY `IX_Hierarchies_Unique_Slug` (`Slug`),
  UNIQUE KEY `IX_Hierarchies_Unique_EntityTypeGuid_And_ParentId_And_Title` (`EntityTypeGuid`,`ParentId`,`Title`),
  KEY `FK_Hierarchies_ParentId_Hierarchies_Id` (`ParentId`),
  CONSTRAINT `FK_Hierarchies_ParentId_Hierarchies_Id` FOREIGN KEY (`ParentId`) REFERENCES `Hierarchies` (`Id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `Ck_Hierarchies_NonEmptyGuid` CHECK (`Guid` <> '00000000-0000-0000-0000-000000000000'),
  CONSTRAINT `Ck_Hierarchies_NonEmptyEntityTypeGuid` CHECK (`EntityTypeGuid` <> '00000000-0000-0000-0000-000000000000'),
  CONSTRAINT `Ck_Hierarchies_NonEmptyImageGuid` CHECK (`ImageGuid` <> '00000000-0000-0000-0000-000000000000'),
  CONSTRAINT `Ck_Hierarchies_NonEmptyIconGuid` CHECK (`IconGuid` <> '00000000-0000-0000-0000-000000000000')
) ENGINE=InnoDB AUTO_INCREMENT=13 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

And I want to have unique titles, per entity type and per parent.

UNIQUE KEY 'IX_Hierarchies_Unique_EntityTypeGuid_And_ParentId_And_Title' ('EntityTypeGuid','ParentId','Title')

And it works fine for all child records that have ParentId. But for root nodes, the records that do not have ParentId and their ParentId value is NULL, this does not work.

That's because MariaDB does not include NULL values for the index.

What can I do?

4
  • 1
    All NULLs are unique - this is a base. If you want NULLs to be treated as definite value and does not allow more than one NULL then you'd replace it with some definite but impossible value. For example this can be CHAR(0) for string-type columns (EntityTypeGuid,Title), and zero (or -1) for numeric columns (ParentId). Depends on DBMS version you may use according generated columns and unique index by them, or index by function.
    – Akina
    Commented Jan 25, 2023 at 7:54
  • FIrst explain what EntityTypeGuid is for and where it comes form.
    – Rick James
    Commented Jan 26, 2023 at 1:41
  • 1
    5 unique keys is "unreasonable" for a table. I don't think I have ever seen a "reasonable" table with more than 2 unique keys.
    – Rick James
    Commented Jan 26, 2023 at 1:42
  • I hope that you don't have more than a few million rows; GUIDs are costly to handle due to their randomness.
    – Rick James
    Commented Jan 26, 2023 at 1:44

1 Answer 1

2

Make top entries in your hierarchy reference a root node (e.g. with ID 0) in ParentId. This includes them in the index. The drawback is that you now need to include … AND Id <> 0 when querying the hierarchy.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.