Skip to main content

Model Relationships

Relationship Types​

Model relationships include three logical column types:

One-to-One Reverse Association​

A special case of one-to-many relationships, implemented as one-to-one through unique index constraints. This field does not physically exist in the table, but is retrieved through LEFT JOIN queries.

Example: The one-to-one relationship between "student ID card" and "student". The one-to-one reverse association field does not actually exist in the student ID card table.

One-to-Many Association​

A record in the current model is associated with multiple records in another model. This field does not physically exist in the table, but is retrieved through LEFT JOIN queries.

Example: One "class" is associated with multiple "students". The one-to-many field does not actually exist in the class table.

Many-to-Many Association​

Multiple records in the current model are associated with multiple records in another model. This field does not physically exist in the table, but is retrieved through junction tables and LEFT JOIN queries.

Example: The course enrollment relationship between "students" and "courses". The many-to-many field does not actually exist in the student table and course table.

Many-to-Many Junction Tables​

Finding Junction Tables​

  1. Go to CloudBase Console/MySQL Database/Data Models, and find the "Junction Model Identifier" corresponding to the junction table in the model field configuration:

    Model Field Configuration

  2. Search the database tables using the "Junction Model Identifier":

    Search Junction Table

💡 Note: Tables whose names end with -preview are junction tables for many-to-many relationships of preview environment models, while tables without the -preview suffix are junction tables for many-to-many relationships of production environment models.

Junction Table Field Descriptions​

  • leftRecordId: Records the record ID of the main table (left table), such as the value corresponding to the primary key _id of the student table
  • rightRecordId: Records the record ID of the sub table (right table), such as the value corresponding to the primary key _id of the course table
Junction Table Association Fields

Foreign Key Constraint Configuration​

Model relationships currently do not support foreign key operations. To add foreign key constraints, please operate on the database table management page in the CloudBase Console.

One-to-One Relationship Foreign Key Constraints​

Application Scenarios​

  • User ↔ User Profile (one user corresponds to one user profile)
  • Student ↔ Student ID Card (one student corresponds to one student ID card)

Console Operation Steps​

  1. Find the table name and association field identifier through the model:

  2. Search for related tables on the database table management page and add foreign keys:

SQL Creation Example​

-- User Profile Table (child table)
CREATE TABLE user_profile (
    _id INT AUTO_INCREMENT PRIMARY KEY,
    user_id INT NOT NULL UNIQUE, -- UNIQUE constraint ensures one-to-one relationship
    avatar VARCHAR(255),
    bio TEXT,
    CONSTRAINT fk_profile_user_id FOREIGN KEY (user_id) 
        REFERENCES user(_id) ON DELETE CASCADE
);

Configuration Key Points​

  • Add a UNIQUE constraint on the foreign key field of the child table
  • Use ON DELETE CASCADE to ensure cascading deletion
  • The foreign key field must be NOT NULL

One-to-Many Relationship Foreign Key Constraints​

Application Scenarios​

  • Department ↔ Employees (one department has multiple employees)
  • Article ↔ Comments (one article has multiple comments)

Console Operation Steps​

  1. Find the table name and association field identifier through the model:

  2. Search for related tables on the database table management page and add foreign keys:

SQL Creation Example​

-- Employee Table (the "many" side)
CREATE TABLE employee (
    _id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50) NOT NULL,
    department_id INT NOT NULL,
    position VARCHAR(100),
    _openid VARCHAR(64),
    CONSTRAINT fk_employee_department_id FOREIGN KEY (department_id) 
        REFERENCES department(_id) ON DELETE CASCADE
);

Configuration Key Points​

  • Foreign key constraints are established on the "many" side
  • The foreign key field is usually NOT NULL
  • Use ON DELETE CASCADE to handle cascading deletion

Many-to-Many Relationship Foreign Key Constraints​

Application Scenarios​

  • Student ↔ Course (one student takes multiple courses, and one course has multiple students)
  • User ↔ Role (one user has multiple roles, and one role is assigned to multiple users)

Console Operation Steps​

  1. Find the junction model identifier corresponding to the junction table through the model field configuration:

  2. Search for related tables on the database table management page using the junction model identifier:

SQL Creation Example​

-- Junction table (association table)
CREATE TABLE mid_55ZTLScHD (
    _id INT AUTO_INCREMENT,
    rightRecordId INT NOT NULL,
    leftRecordId INT NOT NULL,
    _openid VARCHAR(64),
    PRIMARY KEY (id, rightRecordId, leftRecordId), -- composite primary key
    CONSTRAINT fk_sc_student_id FOREIGN KEY (leftRecordId) 
        REFERENCES student(_id) ON DELETE CASCADE,
    CONSTRAINT fk_sc_course_id FOREIGN KEY (rightRecordId) 
        REFERENCES course(_id) ON DELETE CASCADE
);

Configuration Key Points​

  • The junction table contains two foreign key fields
  • Use a composite primary key to ensure the uniqueness of associations
  • Both foreign keys are set to cascade on delete

Configuration Specifications​

Table Naming Rules​

  • Preview Environment Table Name: Model identifier + -preview
  • Production Environment Table Name: Model identifier

Constraint Naming Conventions​

  • Use meaningful constraint names: fk_tablename_fieldname
  • Maintain naming consistency for easy maintenance

Cascade Operation Types​

  • CASCADE: When a record in the primary table is deleted, related records in the secondary table are also deleted
  • SET NULL: When a record in the primary table is deleted, the foreign key field in the child table is set to NULL
  • RESTRICT: Prevents deletion of primary table records referenced by foreign keys
  • NO ACTION: Similar to RESTRICT, but with different check timing