Results 1 to 6 of 6

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

  1. #1
    Join Date
    Apr 2013
    Location
    Modra, Slovakia
    Posts
    265

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

    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)
    13.2 KDE 64bit tumbleweed
    Lenovo G500s, Thinkpad R500

  2. #2
    Join Date
    May 2010
    Location
    Space Colony Lagrange Point 22° à, 77° Ƅ, 56° ɤ, 99° ɜ
    Posts
    3,166

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

    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

    Code:
    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

    Code:
    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. #3
    Join Date
    Apr 2013
    Location
    Modra, Slovakia
    Posts
    265

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

    thanks, finally I succed, first I was doin it according to this tut, http://www.sitepoint.com/mysql-forei...e-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
    13.2 KDE 64bit tumbleweed
    Lenovo G500s, Thinkpad R500

  4. #4
    Join Date
    Apr 2013
    Location
    Modra, Slovakia
    Posts
    265

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

    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 ?
    13.2 KDE 64bit tumbleweed
    Lenovo G500s, Thinkpad R500

  5. #5
    Join Date
    May 2010
    Location
    Space Colony Lagrange Point 22° à, 77° Ƅ, 56° ɤ, 99° ɜ
    Posts
    3,166

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

    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:-


    Code:
    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/...nstraints.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. #6
    Join Date
    Apr 2013
    Location
    Modra, Slovakia
    Posts
    265

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

    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
    13.2 KDE 64bit tumbleweed
    Lenovo G500s, Thinkpad R500

Posting Permissions

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