如何基于customer_id限制product表user_id与对应customer的user_id一致

First, let's correct a couple of critical errors in your product table definition—these would cause issues even before we get to your main requirement:

• The primary key is set to user_id, which doesn't make sense (a single user could have multiple products). It should be product_id.
• Your foreign key for customer_id references customer.customer (a typo—it should be customer.customer_id).
• You have two foreign keys named user_id, which is invalid (constraint names must be unique in a table).

Here's the corrected product table creation SQL:

CREATE TABLE `product` ( 
  `product_id` int(11) NOT NULL auto_increment, 
  `name` varchar(255) NOT NULL, 
  `customer_id` int NOT NULL, 
  `user_id` int NOT NULL, 
  PRIMARY KEY (`product_id`),
  CONSTRAINT `fk_product_user` FOREIGN KEY (`user_id`) REFERENCES `user` (`user_id`),
  CONSTRAINT `fk_product_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`)
);

The most reliable way to enforce this rule is using a composite foreign key—this leverages database-level constraints to guarantee data consistency, which is better than triggers (since constraints can't be bypassed as easily).

Step 1: Add a Unique Index to the customer Table #

First, we need a unique index on customer that combines customer_id and user_id. This is required because foreign keys can only reference columns that are either a primary key or part of a unique index:

ALTER TABLE customer ADD UNIQUE INDEX idx_customer_user (customer_id, user_id);

Step 2: Replace the Single-Column Foreign Keys on product #

Now, we'll remove the individual user_id foreign key from product and add a composite foreign key that links both customer_id and user_id to the customer table's new unique index:

-- Drop the old user_id foreign key first
ALTER TABLE product DROP FOREIGN KEY fk_product_user;

-- Add the composite foreign key
ALTER TABLE product ADD CONSTRAINT fk_product_customer_user 
FOREIGN KEY (customer_id, user_id) 
REFERENCES customer (customer_id, user_id);

This works because the composite foreign key ensures that for every (customer_id, user_id) pair in product, there's an exact matching pair in customer. This enforces your rule automatically—you can't insert a product where user_id doesn't match the user_id of the linked customer_id.

If you're using an older database version that doesn't support composite foreign keys (unlikely for modern MySQL, but possible), you can use triggers to validate or auto-set the user_id.

Option A: Validate the user_id on Insert/Update #

This trigger will throw an error if someone tries to insert/update a product where user_id doesn't match the linked customer's user_id:

DELIMITER //
CREATE TRIGGER trg_product_before_insert
BEFORE INSERT ON product
FOR EACH ROW
BEGIN
  DECLARE linked_user_id INT;
  SELECT user_id INTO linked_user_id FROM customer WHERE customer_id = NEW.customer_id;
  
  IF NEW.user_id != linked_user_id THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Error: product.user_id must match the user_id of the associated customer';
  END IF;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_product_before_update
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
  DECLARE linked_user_id INT;
  SELECT user_id INTO linked_user_id FROM customer WHERE customer_id = NEW.customer_id;
  
  IF NEW.user_id != linked_user_id THEN
    SIGNAL SQLSTATE '45000' 
    SET MESSAGE_TEXT = 'Error: product.user_id must match the user_id of the associated customer';
  END IF;
END //
DELIMITER ;

Option B: Auto-Set user_id from the Customer #

If you don't want users to manually input user_id at all, you can have the trigger automatically populate it based on the customer_id:

DELIMITER //
CREATE TRIGGER trg_product_before_insert
BEFORE INSERT ON product
FOR EACH ROW
BEGIN
  SELECT user_id INTO NEW.user_id FROM customer WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

DELIMITER //
CREATE TRIGGER trg_product_before_update
BEFORE UPDATE ON product
FOR EACH ROW
BEGIN
  SELECT user_id INTO NEW.user_id FROM customer WHERE customer_id = NEW.customer_id;
END //
DELIMITER ;

• The composite foreign key approach is always preferred because it's a native database constraint—no extra code to maintain, and it prevents invalid data at the lowest level.
• If you use triggers, make sure to handle cases where the customer_id in customer is updated (though if customer_id is the primary key, you probably shouldn't be updating it anyway).

内容的提问来源于stack exchange，提问作者nullException