CODEWITHSUNDEEP
mysqlsqldatabase
Andrew Barker
Andrew Barker

Reputation: 41

Only allow one unique foreign key for primary

I have 3 tables in mysql, One is a Company table, the other is a license table and the last is a joining table between both primary keys, When a person adds a company id to the license id in the joining table, it allows multiple companies to exist for one license, this cannot happen, so I need to do something that will only allow one company id for one license id

heres the tables

Table license

    CREATE TABLE `License` (
  `license_id` int(11) NOT NULL AUTO_INCREMENT,
  `license_number` varchar(45) NOT NULL,
  `start_date` date NOT NULL,
  `end_date` date NOT NULL,
  `duration` int(11) NOT NULL,
  `expiry_date` date NOT NULL,
  `product_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`license_id`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=latin1;
;

Company Table

    CREATE TABLE `Company` (
  `company_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) NOT NULL,
  `physical_address` varchar(255) DEFAULT NULL,
  `postal_address` varchar(255) DEFAULT NULL,
  `reseller_id` int(11) DEFAULT NULL,
  PRIMARY KEY (`company_id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=latin1;

and Joining table 

    CREATE TABLE `CompanyLicense` (
  `license_id` int(11) NOT NULL,
  `company_id` int(11) NOT NULL,
  PRIMARY KEY (`license_id`,`company_id`),
  KEY `companlicence_company_fk_idx` (`company_id`),
  CONSTRAINT `companylicense_company_fk` FOREIGN KEY (`company_id`) REFERENCES `Company` (`company_id`) ON DELETE CASCADE ON UPDATE CASCADE,
  CONSTRAINT `companylicense_license_fk` FOREIGN KEY (`license_id`) REFERENCES `License` (`license_id`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

So far i have this 

INSERT INTO CompanyLicense (license_id, company_id) VALUES
('2','6') on duplicate key update license_id = '2';

doesnt seem to do the job

Upvotes: 0

Views: 325

Answers (1)

Quassnoi
Quassnoi

Reputation: 425321

You need to make company unique in companylicense:

ALTER TABLE companylicense ADD UNIQUE KEY (company)

or better yet, make company a field in license instead of having a link table.

Upvotes: 1

Related Questions