MariaDB dynamic joined with two field in reference, is it possible?

Question

Can we have sth like this in MariaDB?

ALTER TABLE `files` ADD
CONSTRAINT `fk_persons_cover`
    FOREIGN KEY (`foreign_key`, `model`)
    REFERENCES `persons` (`uuid`, "persons_cover")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

I want refrence from files to persons table when files.uuid=persons.uuid and files.model="persons_cover"

Now with this code MariaDB said:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"persons_cover")
    ON DELETE NO ACTION
    ON UPDATE NO ACTION' at line 4

Is there a solution?

Edit:

I know can use filelable super/interface table like this solution

Or use some auto generated fields in files table like this solution

but they ar not good solution I think.

At first solution we can't find file 1 (row 1) is associated to what? or can't find list of persons 1 (person row with pk = 1) files

At second solution we should add nullable foreign key field per new association!

Does this answer your question? [MySQL - Conditional Foreign Key Constraints](https://stackoverflow.com/questions/2002985/mysql-conditional-foreign-key-constraints) — Mihe, Sep 17 '22 at 10:16
@Mihe Thanks for suggestion, but not completely, I edit my question — Mehrdad Dadkhah, Sep 17 '22 at 11:56
Not sure I understand this FKs are all or nothing if you want to conditionally enforce referential integrity use a trigger.(or don't bother at all) — P.Salmon, Sep 17 '22 at 12:52

score 1 · Answer 1 · answered Sep 17 '22 at 10:27

It should work, if the PERSONS table has a composite primary key (or unique key), encompassing uuid and persons_cover eg

create table persons( 
  uuid varchar( 100 ) 
, persons_cover varchar( 100 ) 
, constraint persons_pkey primary key ( uuid, persons_cover )
) ;
 
create table files (
  uuid varchar( 100 )
, model varchar( 100 )
) ;
 
alter table files
add constraint fk_persons_cover
foreign key( uuid, model ) references persons( uuid, persons_cover ) ;

If, in your PERSONS table, (only) the UUID column is the primary key column, the composite foreign key may not work. However, in this case, you could add a composite UNIQUE key to the PERSONS table (uuid and person_cover). Should the values stored in the table allow this, then you can add the composite foreign key to FILES.

create table persons2( 
  uuid varchar( 100 ) 
, persons_cover varchar( 100 ) 
, constraint persons_pkey primary key ( uuid )  -- PK: only one column
) ;


-- does not work 
alter table files
add constraint fk_persons_cover2
foreign key( uuid, model ) references persons2( uuid, persons_cover ) ;

-- error
-- Failed to add the foreign key constraint. Missing index for constraint 'fk_persons_cover2' in the referenced table 'persons2'

 
-- add a composite unique key
alter table persons2 
add constraint upc_unique unique( uuid, persons_cover ) ;


-- no problem
alter table files
add constraint fk_persons_cover2
foreign key( uuid, model ) references persons2( uuid, persons_cover ) 
on delete no action
on update no action 
;

DBfiddle here.

Yes, this can be but I need conditional refrence, If it's impossible I should try another solution or don't set refrence and add condition in my queries — Mehrdad Dadkhah, Sep 17 '22 at 11:38
Sorry for wasting your time! I misunderstood the question - I thought that persons_cover was a column name. — stefan, Sep 17 '22 at 16:16

Bill Karwin · Answer 2 · 2022-10-21T23:24:03.833

REFERENCES `persons` (`uuid`, "persons_cover")

No, you can't put a string literal in this line. You must name only column names that exist in the persons table. Typically these are the columns of the primary key of that table.

The foreign key constraint applies to all rows of your table. There's no such thing as a conditional foreign key that only applies on some rows.

I understand you think the workarounds you linked to are not good solutions, but that should be a clue that polymorphic associations are not a good design.

I wrote more about the disadvantages of polymorphic associations in numerous of my answers on Stack Overflow, and in a chapter of my book SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming.

MariaDB dynamic joined with two field in reference, is it possible?

2 Answers2