ensure uniqueness of 2 out of three keys in database-CodePudding

I am trying to store records of attachements uploaded to a system and their parents "folders".

Each folder (in the folders table) can have no parent at all (a root folder) or have a parent that is also in the folders table.

Only problem is I would like to ensure that under the same parent, 2 folders can not have the same name. I need some key triplet (folderid,parentid,foldername)

mysql table:

CREATE TABLE IF NOT EXISTS `folders` (
    `uploaderid` int(11) NOT NULL,
    `parentid` int(11) unsigned NULL,
    `folderid` int(11) unsigned AUTO_INCREMENT,
    `foldername` VARCHAR(255) NOT NULL,
-- @TODO we need a constraint that says uploderid = (select uploaiderid FROM folders where folderid = the New parent id) --> make sur subfolder is created/owned by the same owner of the folder.
    UNIQUE KEY unique_folderid (folderid),
    FOREIGN KEY (parentid) REFERENCES folders(folderid) ON DELETE CASCADE,
    FOREIGN KEY (uploaderID) REFERENCES accounts(id)
    ON DELETE NO ACTION
    

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

problem is I need to allow nullable value for parentid so I can find if the folder is root. But this means I can't make parentid part of a key because a key can not be nullable...

Logically the constraint I need is for any 2 rows: row1.name = row2.name ==> row1.parent is diff from row2.parent

with the current structure I can end up with

Any suggestions to improve my design are much appreciated :)

CodePudding user response：

Use a normal primary key, then use a unique constraint on (parentid, foldername).

However, null equals nothing, not even null. So this will accept multiple root folders with the same name. This is because null means "unknown". Instead, make parentid not null and use a sentinel value to indicate root. Make it refer to itself.

create table folders (
  id bigint primary key auto_increment,
  uploaderid bigint not null,
  parentid bigint not null,
  foreign key (parentid) references folders(id) on delete cascade,
  foldername varchar(255) not null,

  unique(parentid, foldername)
);

-- This is the root folder.
insert into folders (id, uploaderid, parentid, foldername) values (-1, 1, -1, '');

Demonstration

Alternatively, just store the path name. Find subfolders with where path like '/foo/%'; This does require the path names be normalized, do this with an insert trigger or at the application level.

create table folders (
  path varchar(255) primary key,
  uploaderid bigint not null
);

Demonstration.