having trouble with my database using workbench

Question

I am creating a test database to train with and am running into this problem -

"Schema Creation Failed: Can't create table 'db_2_5b129.tbluserassignment' (errno: 150): "

The code for the specific table is -

-- Table tblUserAssignment

CREATE TABLE IF NOT EXISTS `tblUserAssignment` (  
`assignment_id` INT UNSIGNED NOT NULL AUTO_INCREMENT,  
`user_id` INT NOT NULL,  
`supervisor_id` INT NOT NULL,  
`position_id` INT NOT NULL,  
`department_id` INT NOT NULL,  
`start_date` DATE NOT NULL,  
`end_date` DATE NOT NULL,  
`date_added` DATE NOT NULL,  
`date_modified` DATETIME NOT NULL,  
`date_deleted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,         
PRIMARY KEY (`assignment_id`),  
INDEX `fk_tblUserAssignment_tblUserPhone1_idx` (`user_id` ASC),  
INDEX `fk_tblUserAssignment_tblUserPositions1_idx` (`position_id` ASC),  
CONSTRAINT `fk_tblUserAssignment_tblUserPhone1`  
FOREIGN KEY (`user_id`)  
REFERENCES `tblUserPhone` (`user_id`)  
ON DELETE NO ACTION  
ON UPDATE NO ACTION,  
CONSTRAINT `fk_tblUserAssignment_tblUserPositions1`  
FOREIGN KEY (`position_id`)  
REFERENCES `tblUserPositions` (`position_id`)  
ON DELETE NO ACTION  
ON UPDATE NO ACTION)  
ENGINE = InnoDB;

Can someone look it over and point me in the right direction?

what about REFERENCES `tblUserPositions` and REFERENCES `tblUserPhone` ? — Subdigger, May 08 '14 at 05:32
Here are the referenced tables - -- ----------------------------------------------------- -- Table `tblUserPositions` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `tblUserPositions` ( `position_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `description` VARCHAR(100) NOT NULL, `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, `date_deleted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`position_id`)) ENGINE = InnoDB; — pwkim21, May 08 '14 at 06:11
-- ----------------------------------------------------- -- Table `tblUserPhone` -- ----------------------------------------------------- CREATE TABLE IF NOT EXISTS `tblUserPhone` ( `contact_information_id` INT UNSIGNED NOT NULL AUTO_INCREMENT, `user_id` INT NOT NULL, `phone_number` VARCHAR(20) NOT NULL, `phone_type_id` INT NOT NULL, `date_added` DATETIME NOT NULL, `date_modified` DATETIME NOT NULL, `date_deleted` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (`contact_information_id`,`user_id`)) ENGINE = InnoDB; — pwkim21, May 08 '14 at 06:13

score 0 · Accepted Answer · edited May 23 '17 at 12:20

0

problem is in differences between forign fields type

tblUserPositions.position_id INT UNSIGNED NOT NULL AUTO_INCREMEN

and

tblUserAssignment.position_id INT NOT NULL,`

EDIT 1

see this Use Composite Primary Key as Foreign Key

EDIT 2

this one tested & works fine

CREATE TABLE `tbluserphone` (
    `contact_information_id` INT(10) UNSIGNED NOT NULL,
    `user_id` INT(11) NOT NULL,
    `phone_number` VARCHAR(20) NOT NULL,
    `phone_type_id` INT(11) NOT NULL,
    `date_added` DATETIME NOT NULL,
    `date_modified` DATETIME NOT NULL,
    `date_deleted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`contact_information_id`, `user_id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

CREATE TABLE `tbluserassignment` (
    `assignment_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `contact_information_id` INT(10) UNSIGNED NOT NULL,
    `user_id` INT(11) NOT NULL,
    `supervisor_id` INT(11) NOT NULL,
    `position_id` INT(10) UNSIGNED NOT NULL,
    `department_id` INT(11) NOT NULL,
    `start_date` DATE NOT NULL,
    `end_date` DATE NOT NULL,
    `date_added` DATE NOT NULL,
    `date_modified` DATETIME NOT NULL,
    `date_deleted` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (`assignment_id`),
    INDEX `fk_tblUserAssignment_tblUserPhone1_idx` (`user_id`),
    INDEX `fk_tblUserAssignment_tblUserPositions1_idx` (`position_id`),
    INDEX `contact` (`contact_information_id`),
    CONSTRAINT `FK_tbluserassignment_tbluserpositions`
         FOREIGN KEY (`position_id`)
         REFERENCES `tbluserpositions` (`position_id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
   CONSTRAINT tbluserassignment_fkz1
         FOREIGN KEY (contact_information_id, user_id)
         REFERENCES tbluserphone (contact_information_id, user_id)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;

(contact_information_id, user_id) - fields should goes same way, like in primary key difinition

edited May 23 '17 at 12:20

Community

1
1

answered May 08 '14 at 06:26

Subdigger

2,166
3
20
42

So I added the "UNSIGNED" to the `position_id` under `tblUserAssignment`. Considering the `user_id` under `tblUserPhone` is set as a primary key as well, I tried running it and am still getting the error. – pwkim21 May 08 '14 at 06:47
Scratch that last comment, this is what I was trying to write: So under tblUserAssignment.position_id, I changed it to - `position_id` INT UNSIGNED NOT NULL, Since I can't have two AUTO_INCREMEMT under one table, is there something else I am missing because I am still getting the same error as before. – pwkim21 May 08 '14 at 06:53
Yeah just saw it, I think it was a little slow to reload. Reading it now and trying to understand it haha thanks a lot. I'll post my progress. – pwkim21 May 08 '14 at 07:02
So I tinkered with it, and am still getting an error. Sorry. CONSTRAINT `pk_assignment` PRIMARY KEY (`assignment_id`), CONSTRAINT `fk_posituserID` FOREIGN KEY (`position_id`) REFERENCES `tblUserPositions`(`position_id`), FOREIGN KEY (`user_id`) REFERENCES `tblUserPhone`(`user_id`)) – pwkim21 May 08 '14 at 07:16
@pwkim21 by that likn you should a) make primary key single filed and then us like u r trying; or b) add to table `tblUserAssignment` all neded columns (with same data types) to match PRIMARY KEY (contact_information_id,user_id) key from master table. and than use composite forign key `FOREIGN KEY (contact_information_id,user_id) REFERENCES student (contact_information_id,user_id)` – Subdigger May 08 '14 at 07:28
AWESOME! Thank you so much for writing it out. I wasn't exactly sure what you meant in your post before. Will try to implement and deconstruct to under. – pwkim21 May 08 '14 at 20:02
Okay maybe im missing something and it's not letting me post my code again or chat since im new here. You think I can hit you up for some help? – pwkim21 May 08 '14 at 20:52

having trouble with my database using workbench

1 Answers1