Re: need help creating relation

2011-05-23 Thread Jo�o C�ndido de Souza Neto 
I could be wrong but I think your problem is the unique index.

-- 
João Cândido de Souza Neto

robert rottermann rob...@redcor.ch escreveu na mensagem 
news:4dd967a8.5040...@redcor.ch...
 Hi there,

 I would like to create a table that optionally links to an other table.

 The field company_id in the following table should be either Null or link 
 to the table tblCompany (`id` ).
 How do I do that. The way I it now, I can not enter records where the 
 field company_id is null.

 Thanks
 robert

 drop table IF EXISTS tblAgentCompanyMapper;
 CREATE  TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` (
   `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' ,
   `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used 
 by energiecluster adresses' ,
   `name` VARCHAR(128) NOT NULL ,
   `url` VARCHAR(256) NULL ,
   INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) ,
   PRIMARY KEY (`siaid`) ,
   CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1`
 FOREIGN KEY (`company_id` )
 REFERENCES `energie_2`.`tblCompany` (`id` )
 ON DELETE SET NULL
 ON UPDATE NO ACTION)
 ENGINE = InnoDB
 DEFAULT CHARACTER SET = utf8;
 



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

need help creating relation

2011-05-22 Thread robert rottermann 

Hi there,

I would like to create a table that optionally links to an other table.

The field company_id in the following table should be either Null or link to the 
table tblCompany (`id` ).
How do I do that. The way I it now, I can not enter records where the field 
company_id is null.


Thanks
robert

drop table IF EXISTS tblAgentCompanyMapper;
CREATE  TABLE IF NOT EXISTS `energie_2`.`tblAgentCompanyMapper` (
  `siaid` INT COMMENT 'id assigned in the excelsheet by SIA' ,
  `company_id` INT NULL DEFAULT NULL UNIQUE COMMENT 'id automatically used by 
energiecluster adresses' ,

  `name` VARCHAR(128) NOT NULL ,
  `url` VARCHAR(256) NULL ,
  INDEX `fk_tblAgentCompanyMapper_tblCompany1` (`company_id` ASC) ,
  PRIMARY KEY (`siaid`) ,
  CONSTRAINT `fk_tblAgentCompanyMapper_tblCompany1`
FOREIGN KEY (`company_id` )
REFERENCES `energie_2`.`tblCompany` (`id` )
ON DELETE SET NULL
ON UPDATE NO ACTION)
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8;


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org