1

Table One:

CREATE TABLE `environment` (
  `ID` int(11) NOT NULL AUTO_INCREMENT,
  `APPLICATION` varchar(5) NOT NULL,
  `APPLICATION_ID` varchar(42) DEFAULT NULL,
  PRIMARY KEY (`ID`,`APPLICATION`)
  ) ENGINE=InnoDB

Table Two:

CREATE TABLE `properties_files` (
  `FILE_NAME` varchar(254) COLLATE latin1_bin NOT NULL,
  `APPLICATION` varchar(10) COLLATE latin1_bin NOT NULL,
   KEY `fk_PROPERTIES_FILES_ENVIRONMENT` (`APPLICATION`),
   CONSTRAINT `fk_PROPERTIES_FILES_ENVIRONMENT` FOREIGN KEY (`APPLICATION`) REFERENCES `ENVIRONMENT` (`APPLICATION`) ON DELETE NO ACTION ON UPDATE NO ACTION
  ) ENGINE=InnoDB

Now My requirement is how to set the foreign key in table two properties_files column name is Application.

Could you please help me on this ?

Michał Szkudlarek
  • 1,443
  • 1
  • 21
  • 35
viswa
  • 51
  • 1
  • 9

2 Answers2

2

You have to have the same structure (data types) of the referenced columns (in this situation you have to decide on:

  • varchar(5) or varchar(10)
  • NOT NULL or COLLATE latin1_bin NOT NULL

This is just a starting point, you also have to "mark" referenced column as a key/index/unique index. Please refer to this answer for details: Can a foreign key reference a non-unique index?

Which means you need to add something like that in your ENVIRONMENT table:

KEY YOUR_KEY_OR_INDEX_NAME(APPLICATION)

Added working example here:

DEMO HERE

Community
  • 1
  • 1
Michał Szkudlarek
  • 1,443
  • 1
  • 21
  • 35
0

Your foreign key column properties are different in master and child, please use below syntax.

Also index is missing on referenced column.

CREATE TABLE environment (  ID INT(11) NOT NULL AUTO_INCREMENT,  APPLICATION VARCHAR(5) NOT NULL,  APPLICATION_ID VARCHAR(42) DEFAULT NULL, PRIMARY KEY (ID,APPLICATION), KEY idx_ap(APPLICATION) ) ENGINE=INNODB

CREATE TABLE properties_files (  FILE_NAME VARCHAR(254) COLLATE latin1_bin NOT NULL,  APPLICATION VARCHAR(5) NOT NULL, KEY fk_PROPERTIES_FILES_ENVIRONMENT (APPLICATION), CONSTRAINT fk_PROPERTIES_FILES_ENVIRONMENT FOREIGN KEY (APPLICATION) REFERENCES ENVIRONMENT (APPLICATION) ON DELETE NO ACTION ON UPDATE NO ACTION ) ENGINE=INNODB
Zafar Malik
  • 6,734
  • 2
  • 19
  • 30
  • Hi Zafar, Your Your code is working for me Thanks lot. Could you please guide me how to learn the character set and collation ? there is any documentation ? – viswa Sep 10 '15 at 06:33
  • If it worked for you then you may choose it as accepted answer. :) – Zafar Malik Sep 10 '15 at 06:36
  • @viswa Please look at this link and take note of the green tick: http://stackoverflow.com/tour – foxidrive Oct 01 '15 at 04:11