CODEWITHSUNDEEP
mysqlforeign-keysrelational-database
user137
user137

Reputation: 759

MySQL relationship with 2 table

I have two tables:

CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(255) DEFAULT NULL,
`industry_id` int(11) DEFAULT NULL,
 ... other fields ...
`created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
`updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
 CURRENT_TIMESTAMP,
 PRIMARY KEY (`id`) 
 ) ENGINE=InnoDB DEFAULT CHARSET=latin1;

and:

CREATE TABLE `industries` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `created_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

I tried to add a foreign key to the second table:

ALTER TABLE industries ADD FOREIGN KEY (id) REFERENCES companies(industry_id) ON DELETE RESTRICT;

But I got next error:

Cannot add foreign key constraint

How can I add right fkey?

Upvotes: 0

Views: 33

Answers (1)

Nick
Nick

Reputation: 702

I suppose you've done it wrong. Primary key can't be Foreign Key. Let's make little changes, which can give you the same value

ALTER TABLE companies ADD FOREIGN KEY (industry_id) REFERENCES industries(id) ON DELETE RESTRICT;

Upvotes: 1

Related Questions