need help creating relation

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=gcdmg-mysql-2@m.gmane.org

Re: need help creating relation

I could be wrong but I think your problem is the unique index.

--
João Cândido de Souza Neto

"robert rottermann" escreveu na mensagem
news:4DD967A8.5040206@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=gcdmg-mysql-2@m.gmane.org