cannot ALTER TABLE (add forign key) #1005 - Can't create...#sql-12db_9f' (errno: 150)

English Programming/Scripting
1

I’ve 2 tables users (id is primary key) and user_content (id2 is primary) both of them smallint (6) and I wanna make foreign key so everything from user_content links to particular user (id i.e.) so I tried this

ALTER TABLE `user_content`
ADD CONSTRAINT `FK_id`
FOREIGN KEY (`id2`) REFERENCES `users(id)`
ON UPDATE CASCADE
ON DELETE CASCADE ;

but I ge this error

 #1005 - Can't create table 'skusobna.#sql-12db_9f' (errno: 150)

(both tables exists)

2

roberto68 wrote:
>
> I’ve 2 tables users (id is primary key) and user_content (id2 is
> primary) both of them smallint (6) and I wanna make foreign key so
> everything from user_content links to particular user (id i.e.) so I
> tried this
>
>
> PHP code:
> --------------------
> ALTER TABLE user_content
> ADD CONSTRAINT FK_id
> FOREIGN KEY (id2) REFERENCES users(id)
> ON UPDATE CASCADE
> ON DELETE CASCADE ;
> --------------------
> but I ge this error
>
>
> PHP code:
> --------------------
> #1005 - Can’t create table ‘skusobna.#sql-12db_9f’ (errno: 150)
>
> --------------------
> (both tables exists)
>
>

If your table structure is this


CREATE TABLE users(
user_id smallint (6) not null auto_increment primary key,
user_name varchar(255) not null,
cat_description text
)
CREATE TABLE user_content(
content_id smallint (6) not null auto_increment primary key,
content_name varchar(355) not null,
)ENGINE=InnoDB;

then do this


ALTER TABLE user_content
ADD COLUMN user_id smallint (6) not null AFTER content_name;


ALTER TABLE user_content
ADD FOREIGN KEY fk_users(user_id)
REFERENCES users(user_id)
ON DELETE CASCADE
ON UPDATE CASCADE;

Refer :- http://www.mysqltutorial.org/mysql-foreign-key/

–
GNOME 3.10.2
openSUSE 13.1 (Bottle) (x86_64) 64-bit
Kernel Linux 3.11.6-4-desktop

3

thanks, finally I succed, first I was doin it according to this tut, http://www.sitepoint.com/mysql-foreign-keys-quicker-database-development/ > conclusion it’s good to add another column (which is not priamry, auto inc), so I don’t need to mess with that constraint

4

and I’ve another question related - I want to have matched user_id - content_id so for one user_id’d be only one content_id, I’ve simple registration form (coded in php), so if user doesn’t enter content information it 've to insert there some defalut so that content_id 'd increment. And here it comes : is there option to restrict that foreign key to only column : like only one row with that particular user_id form table users ?

5

roberto68 wrote:
>
> and I’ve another question related - I want to have matched user_id -
> content_id so for one user_id’d be only one content_id, I’ve simple
> registration form (coded in php), so if user doesn’t enter content
> information it 've to insert there some defalut so that content_id 'd
> increment. And here it comes : is there option to restrict that foreign
> key to only column : like only one row with that particular user_id form
> table users ?
>
>

The ideal solution would be creating a users table and a contents table
and then have a transactional table mapping user id with content id

Example:-



Referenced Table 1
-------------------------
CREATE TABLE `study_mode` (
`id_study_mode` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`code` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_study_mode`))
ENGINE = InnoDB;

Referenced Table 2
-------------------------
CREATE TABLE `gender` (
`id_gender` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(45) NOT NULL,
`code` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_gender`))
ENGINE = InnoDB;

Referencing Table with FKs
----------------------------------
CREATE TABLE `prospect` (
`id_prospect` INT NOT NULL AUTO_INCREMENT,
`f_name` VARCHAR(45) NOT NULL,
`l_name` VARCHAR(45) NOT NULL,
`gender` VARCHAR(45) NOT NULL,
`e_mail` VARCHAR(45) NOT NULL,
`birth_year` YEAR NOT NULL,
`study_mode` VARCHAR(45) NOT NULL,
PRIMARY KEY (`id_prospect`),
UNIQUE INDEX (`id_prospect`, `e_mail`),
INDEX (`gender`),
FOREIGN KEY (`gender`)
REFERENCES `gender`(`code`)
ON DELETE CASCADE ON UPDATE CASCADE,
INDEX (`study_mode`),
FOREIGN KEY (`study_mode`)
REFERENCES `mydb`.`study_mode`(`code`)
ON DELETE CASCADE ON UPDATE CASCADE)
ENGINE = InnoDB;

Source:-
http://dev.mysql.com/doc/refman/5.6/en/innodb-foreign-key-constraints.html

I recommend that you read about how you create or organize a proper
relational database. It will help you in the wrong run and will reduce
future “reworking” in the database structure.

source:- http://www.phlonx.com/resources/nf3/nf3_tutorial.pdf

–
GNOME 3.10.2
openSUSE 13.1 (Bottle) (x86_64) 64-bit
Kernel Linux 3.11.6-4-desktop

6

thanks for the clear proper explanation thru this another “mapping” table. you know when I looked at foreign key - there were just simple examples. and aother thing I use it in user registration (PHP), so when new user is created it automaticaly insert blank data to user content so the id would increment for sure. But for the big “enterprise” purpose your suggestion is best