MYSQL Relationship not working

Radzen Studio Blazor Server
1

I have a straightforward MYSQL database with a many to one relationship (based on a foreign key). When I add it to Radzen, it does not pull this many to one relationship. When I tried the Master and Detail page, it is not detecting the schema either. I can create CRUD pages for both tables, but it is not linking them.

MYSQL basic layout

CREATE SCHEMA IF NOT EXISTS contacts DEFAULT CHARACTER SET utf8mb4 ;
USE contacts ;

-- Table contacts.contacttype

CREATE TABLE IF NOT EXISTS contacts.contacttype (
contactid INT NOT NULL AUTO_INCREMENT,
ContactType VARCHAR(255) CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NOT NULL,
PRIMARY KEY (contactid),
UNIQUE INDEX contactid_UNIQUE (contactid ASC) VISIBLE)
ENGINE = InnoDB
AUTO_INCREMENT = 5
DEFAULT CHARACTER SET = utf8;

-- Table contacts.contact

CREATE TABLE IF NOT EXISTS contacts.contact (
idProfile INT NOT NULL AUTO_INCREMENT,
Name TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
Telephone TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
FirstName TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
LastName TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
Street TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
City TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
State TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
ZIP TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
AccountNumber TINYTEXT CHARACTER SET 'utf8mb4' COLLATE 'utf8mb4_0900_ai_ci' NULL DEFAULT NULL,
contacttype_contactid INT NOT NULL,
PRIMARY KEY (idProfile, contacttype_contactid),
UNIQUE INDEX idProfile_UNIQUE (idProfile ASC) VISIBLE,
INDEX fk_contact_contacttype_idx (contacttype_contactid ASC) VISIBLE,
CONSTRAINT fk_contact_contacttype
FOREIGN KEY (contacttype_contactid)
REFERENCES contacts.contacttype (contactid)
ON DELETE NO ACTION
ON UPDATE NO ACTION)
ENGINE = InnoDB
AUTO_INCREMENT = 1005
DEFAULT CHARACTER SET = utf8;

3

Master/Detail and hierarchy templates can work only with one-to-many relationships.

4

Correct so in my test case I should be able to have "Master" of ContactType, and "Detail" of contact. Also, do you know why it is not respecting or pulling the relationship between the tables on the CRUD pages? I noticed on MS SQL there is a checkbox for retrieving relationships, but this feature is not on MY SQL.

5

For MySQL data source this is by default while for MSSQL you have an option to not retrieve them.

6

I am sorry, I think I am missing something. In Radzen it is not linking the relationship data. For example, if I take the CRUD created DataGrid for Contacts, it gives me the Contacttype ID, but I want the contact type. Which it should know from in this case a many to one relationship. It is not giving me the related field to display.

7

Here is how Radzen will retrieve the relations:

SELECT DISTINCT
      CONCAT(k.CONSTRAINT_NAME,'_', i.table_name, '_', k.REFERENCED_TABLE_NAME) AS foreignKeyName,
      k.TABLE_CATALOG as dependentSchema,
      i.TABLE_NAME as dependentTable,
      k.COLUMN_NAME as foreignKey,
      k.CONSTRAINT_CATALOG as parentSchema,
      k.REFERENCED_TABLE_NAME as parentTable,
      k.REFERENCED_COLUMN_NAME as principalKey
      FROM information_schema.TABLE_CONSTRAINTS i
      LEFT JOIN information_schema.KEY_COLUMN_USAGE k ON i.CONSTRAINT_NAME = k.CONSTRAINT_NAME
      WHERE i.CONSTRAINT_TYPE = 'FOREIGN KEY'
      AND i.TABLE_SCHEMA = 'YOURDATABASE'

If the relationship is not part of information_schema Radzen will not be able to retrieve it.

8

Hi, Just a comment as it has taken me a long time (maybe i am slow :grinning: ), is that you need to create the CRUD again with in Edit data source. If you just add the CRUD via the page creation it does not include this. Thanks