带外键的user表建表：需实现用户多关联及组合唯一约束

Hey there! The issue with your current table setup is that the primary key uid is auto-incrementing—so every insert will create a new row even if the user_name+cid+rid combination already exists. To enforce your requirement of blocking duplicate combinations of these three fields, you need to add a unique constraint on the trio of user_name, cid, and rid.

Modified Create Table Statement #

Here's the adjusted code that implements this constraint:

CREATE TABLE IF NOT EXISTS `user` (
  `uid` INT AUTO_INCREMENT,
  `user_name` varchar(100) NOT NULL,
  `cid` INT NOT NULL,
  `rid` INT NOT NULL,
  PRIMARY KEY (`uid`),
  FOREIGN KEY (`cid`) REFERENCES company(`cid`),
  FOREIGN KEY (`rid`) REFERENCES role(`rid`),
  -- This unique constraint blocks duplicate (user_name, cid, rid) combinations
  UNIQUE KEY `unique_user_company_role` (`user_name`, `cid`, `rid`)
) DEFAULT CHARACTER SET latin1 COLLATE latin1_general_cs;

What This Does #

• The UNIQUE KEY constraint ensures that no two rows can have the exact same combination of user_name, cid, and rid. If someone tries to insert a duplicate combination, the database will throw an error and reject the insert.
• It still fully supports your other requirements:
  • A single user can have multiple rows with different cid/rid values (each unique combination gets its own uid)
  • Multiple users can share the same cid or rid (as long as their full combination with user_name is unique)

If the Table Already Exists #

If you've already created the user table and just need to add the constraint retroactively, run this alter statement:

ALTER TABLE `user`
ADD UNIQUE KEY `unique_user_company_role` (`user_name`, `cid`, `rid`);

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