SQL TABLE -MAPPING- FOREIGN KEY RELATION

CREATION & DROP

//Table-----------------Application---------
DROP TABLE IF EXISTS `myschema`.`application`;
CREATE TABLE  `myschema`.`application` (
 `APPLICATION_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `APPLICATION_NAME` varchar(255) NOT NULL,
 `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `APPLICATION_NAME` (`APPLICATION_NAME`),
  PRIMARY KEY (`APPLICATION_ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;


//Table-----------------service-------------
DROP TABLE IF EXISTS `myschema`.`service`;
CREATE TABLE  `myschema`.`service` (
 `SERVICE_ID` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `SERVICE_NAME` varchar(255) NOT NULL,
 `CREATED_AT` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  UNIQUE KEY `SERVICE_NAME` (`SERVICE_NAME`),
  PRIMARY KEY (`SERVICE_ID`)
 ) ENGINE=InnoDB DEFAULT CHARSET=utf8;



//--Mapping Table------------App and Serv---

DROP TABLE IF EXISTS `myschema`.`service`;
CREATE TABLE `myschema`.`app_serv` (
  APPLICATION_ID int(11) unsigned NOT NULL,
  SERVICE_ID int(11) unsigned NOT NULL,
  SERV_ORDER int(11) DEFAULT NULL,
  PRIMARY KEY (APPLICATION_ID,SERVICE_ID),
  KEY FK_APP_ID (APPLICATION_ID),
  KEY FK_SER_ID (SERVICE_ID),
  CONSTRAINT FK_APP_ID FOREIGN KEY (APPLICATION_ID) REFERENCES application (APPLICATION_ID),
  CONSTRAINT FK_SER_ID FOREIGN KEY (SERVICE_ID) REFERENCES service (SERVICE_ID)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT & SELECT

//---insert---
INSERT INTO myschema.application(APPLICATION_NAME) VALUES ("my application");
INSERT INTO myschema.service(SERVICE_NAME) VALUES ("my service");

INSERT INTO myschema.app_serv(APPLICATION_ID,SERVICE_ID,SERV_ORDER) VALUES (1,1,1);

//---select----

SELECT * FROM myschema.application a;
SELECT * FROM myschema.service s;


SELECT * FROM myschema.app_serv ts;