[SOLVED] Relation troubles

[smokewon]

Hi there im just trying to create a simple relationship between two tables between table order_routers and router_models

 

Im trying to make the column order_model from table order_routers a foreign key referenced to the table router_model(rModel_ID)

 

however i keep getting: ERROR 1005 (HY000): Can't create table './ecom_dev/order_routers.frm' (errno: 150)

 

I'm in need of desperate help, so any advice/solutions for this - greatly appreciated

 

CREATE  TABLE IF NOT EXISTS `ecom_dev`.`order_routers` (
  `order_router_ID` SMALLINT(6) NOT NULL ,
  `order_model` TINYINT(4) NOT NULL ,
  PRIMARY KEY (`order_router_ID`) ,
  CONSTRAINT `order_routers_ibfk_1`
    FOREIGN KEY (`order_router_ID` )
    REFERENCES `ecom_dev`.`orders` (`order_account_ID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE,
  CONSTRAINT `order_model`
    FOREIGN KEY (`order_model` )
    REFERENCES `ecom_dev`.`router_models` (`rModel_ID` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

 

CREATE  TABLE IF NOT EXISTS `ecom_dev`.`router_models` (
  `rModel_Brand_ID` TINYINT(4) NOT NULL ,
  `rModel_ID` TINYINT(4) NOT NULL ,
  `rModel_name` VARCHAR(20) NOT NULL ,
  `rModel_type` VARCHAR(20) NOT NULL ,
  `rModel_speed` VARCHAR(12) NOT NULL ,
  `rModel_RJ45` TINYINT(4) NOT NULL ,
  `rModel_price` DECIMAL(6,2) NOT NULL ,
  PRIMARY KEY (`rModel_Brand_ID`, `rModel_ID`) ,
  CONSTRAINT `router_models_ibfk_1`
    FOREIGN KEY (`rModel_Brand_ID` )
    REFERENCES `ecom_dev`.`router_brands` (`rBrand_ID` )
    ON DELETE CASCADE
    ON UPDATE CASCADE)
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

 

CREATE  TABLE IF NOT EXISTS `ecom_dev`.`router_brands` (
  `rBrand_ID` TINYINT(4) NOT NULL AUTO_INCREMENT ,
  `rBrand_name` VARCHAR(20) NOT NULL ,
  PRIMARY KEY (`rBrand_ID`) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = latin1;

[AjBaz100]

What are the permissions in your table directory, sounds to me like you don't have permission.

 

For example, lets say I have a database called 'mydb'

then if I do a ls on the directory

 

/var/lib/mysql

 

I should see an entry as follows:

drwx------ mysql mysql 4096 XXXX mydb. which means only user mysql can read write and execute any files in this directory.

 

Cheers.

 

 

 

 

[smokewon]

Hi there, thanks for the reply

 

here is what

ls -l /var/lib

shows:

 

drwxr-x--- 8 mysql      mysql     328 2008-08-21 11:06 mysql

 

and

 

ls -l /var/lib/mysql
drwx------ 2 mysql mysql      264 2008-08-11 10:01 bookstore
drwx------ 2 mysql mysql      328 2008-08-21 16:02 ecom_dev
drwx------ 2 mysql mysql      224 2008-08-13 07:03 ecom_final
-rw-rw---- 1 mysql mysql  5242880 2008-08-21 16:03 ib_logfile0
-rw-rw---- 1 mysql mysql  5242880 2008-08-10 12:32 ib_logfile1
-rw-rw---- 1 mysql mysql 10485760 2008-08-21 16:03 ibdata1
drwx------ 2 mysql mysql     1752 2008-08-10 12:36 mysql
drwx------ 2 mysql mysql       48 2008-08-10 12:36 test

 

[AjBaz100]

Uhmm, the permissions look ok, can you do an ls -l of the actual form?

 

/var/lib/mysql/ecom_dev/order_routers.frm

 

[smokewon]

Uhmm, the permissions look ok, can you do an ls -l of the actual form?

 

/var/lib/mysql/ecom_dev/order_routers.frm

 

 

I dont think this is a permissions problem, i have no trouble creating tables, its the relationship for these particular tables im struggling with, but i've found out why:

trying to reference order_routers.order_model to router_models.rModel_ID, unfortunately, router_models.rModel_ID is not unique -- it is part of the primary key, but it is not unique by itself

[AjBaz100]

Glad you solved it.