Results 1 to 2 of 2
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Question Unanswered: How to create a unique Foreign Key?

    Hi,
    I have a "t_users" table and a "t_departments" table:
    Code:
    CREATE TABLE `t_users` (
    	`userId` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`mayLogIn` TINYINT(1) UNSIGNED NOT NULL DEFAULT '0',
    	`userRealName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	`userLoginName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	`userPasswordHash` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	PRIMARY KEY (`userId`)
    )
    ENGINE=InnoDB;
    CREATE TABLE `t_user_departments` (
    	`id` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`parentUserId` MEDIUMINT(8) UNSIGNED NOT NULL,
    	`departmentId` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '1',
    	PRIMARY KEY (`id`),
    	INDEX `FK_user_departments` (`departmentId`),
    	INDEX `FK_user_id` (`parentUserId`),
    	CONSTRAINT `FK_t_user_departments_t_users` FOREIGN KEY (`parentUserId`) REFERENCES `t_users` (`userId`),
    	CONSTRAINT `FK_t_user_departments` FOREIGN KEY (`departmentId`) REFERENCES `t_departments` (`departmentId`)
    )
    ENGINE=InnoDB;
    So a user can be a member of more than one department.
    I want to allow a "userLoginName" only once for any given department, and also allow a "userRealName" only once for any given department.

    If a user was only allowed to be member of a single department, the table and constraints would look like this:
    Code:
    CREATE TABLE `at_users` (
    	`userId` MEDIUMINT(8) UNSIGNED NOT NULL AUTO_INCREMENT,
    	`departmentId` MEDIUMINT(8) UNSIGNED NOT NULL DEFAULT '1',
    	`userRealName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	`userLoginName` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	`userPasswordHash` VARCHAR(50) NOT NULL DEFAULT '' COLLATE 'utf8_general_ci',
    	PRIMARY KEY (`userId`),
    	UNIQUE INDEX `userRealName` (`userRealName`, `departmentId`),
    	UNIQUE INDEX `userLoginName` (`userLoginName`, `departmentId`),
    	INDEX `FK_departments` (`departmentId`),
    	CONSTRAINT `FK_user_departments` FOREIGN KEY (`departmentId`) REFERENCES `t_departments` (`departmentId`),
    )
    ENGINE=InnoDB;
    How do I set this up for multiple departments and foreign keys?

    Thanks for any ideas,
    Udo

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so if a user can be a member of more than one department then

    Code:
    PRIMARY KEY (`userId`, `departmentId`),
    OR
    Code:
    PRIMARY KEY ( `departmentId`, `userId`),
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •