MySQL Circular References in One-to-Many Relation: Is there a way to avoid it?-CodePudding

Consider following as an example:

I have a User table which contains user's information along with a PrimaryAddress column that references Address table.

The Address table, contains address information along with a UserId column which refers to who the address belongs to.

Each user can have many addresses, but only one address can be PrimaryAddress. Therefore, the User table needs to store a reference to PrimaryAddress to enforce this rule. Having a IsPrimary column in address table would not have a similar effect.

But as you can tell, this will create a circular relation between User and Address tables and circular dependencies can be a sign of bad design as far as I'm aware. The question is, is there a way to avoid this? If so, how?

CodePudding user response：

A circular reference is not necessarily a "bad" design. You gave an example of a real-world case that has legitimate meaning.

I admit it's a little bit complex to manage. The User.PrimaryAddress must be nullable, if you need to create a user row before you create the address you will eventually designate as the primary address.

Also if you need an SQL script to recreate the database, you can add foreign key constraints only after the referenced table is created. So you have a chicken-and-egg problem if you have circular references. The typical solution is to create tables without their foreign keys, then once they are created, add the foreign keys using ALTER TABLE.

The workarounds all sacrifice something. If you add an attribute Address.IsPrimary, then you have to figure out how to ensure that exactly one address per user is primary. If you use a third table, you have to worry that it is missing a row for each user.

The circular reference may be the least problematic solution.

CodePudding user response：

no you would use a bridge4 table in combination with a BEFORE INSERT TRIGGER

This will also help, if a address can have multiple users

That would then look like

CREATE tABLE user (id int PRIMARY KEY)

INSERT INTO user VALUEs(1)

CREATE TABLe address(id int PRIMARY KEY)

INSERt INTO address VALUES(1),(2)

CREATE TABLE user_address ( user_id int, address_id int, Is_primary int
,FOREIGN KEY (user_id)
        REFERENCES user(id)
        ,FOREIGN KEY (address_id)
        REFERENCES address(id)
        ,PRIMARY KEY (user_id,address_id)

)

CREATE TRIGGER before_user_address_insert
BEFORE INSERT
ON user_address FOR EACH ROW
BEGIN
    DECLARE rowcount INT;
    IF NEW.Is_primary = 1 then
        SELECT COUNT(*) 
        INTO rowcount
        FROM user_address WHERE user_id = NEw.user_id AND Is_primary = 1;
    
        IF rowcount = 1 THEN
        set @message_text = CONCAT('userid ',New.user_id ,' has already a primary address');
            SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = @message_text;
        END IF; 
    end if;

END

INSERT INTO user_address VALUEs(1,1,1)

✓

INSERT INTO user_address VALUEs(1,2,1)

userid 1 has already a primary address

db<>fiddle here