mayor-orig/mayor-naplo/update/pre002651-2-naplo.sql


1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45

CREATE TABLE IF NOT EXISTS `koszi` (                                                                                                                                                                                     
  `kosziId` int(10) unsigned NOT NULL auto_increment,                                                                                                                                                      
  `kosziEsemenyId` int(10) unsigned NOT NULL,                                                                                                                                                              
  `dt` DATE NULL,                                                                                                                                                                                          
  `tanev` smallint(5) unsigned NULL,                                                                                                                                                                       
  `felev` tinyint(3)  unsigned NULL,                                                                                                                                                                       
  `igazolo` set('diák','tanár','osztályfőnök') DEFAULT NULL,                                                                                                                                               
  PRIMARY KEY (`kosziId`),                                                                                                                                                                                 
  CONSTRAINT `koszi_ibfk_1` FOREIGN KEY (`kosziEsemenyId`) REFERENCES `%INTEZMENYDB%`.`kosziEsemeny` (`kosziEsemenyId`) ON DELETE CASCADE ON UPDATE CASCADE                                                         
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;                                                                                                                                            
CREATE TABLE IF NOT EXISTS `kosziIgazoloDiak` (                                                                                                                                                                          
  `kosziId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  `diakId` int(10) unsigned NOT NULL,                                                                                                                                                                      
  PRIMARY KEY (`kosziId`,`diakId`),                                                                                                                                                                        
  CONSTRAINT `kosziIgazoloDiak_ibfk_1` FOREIGN KEY (`kosziId`) REFERENCES `koszi` (`kosziId`) ON DELETE CASCADE ON UPDATE CASCADE,                                                                         
  CONSTRAINT `kosziIgazoloDiak_ibfk_2` FOREIGN KEY (`diakId`) REFERENCES `%INTEZMENYDB%`.`diak` (`diakId`) ON DELETE CASCADE ON UPDATE CASCADE                                                                      
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;                                                                                                                                            
CREATE TABLE  IF NOT EXISTS `kosziIgazoloTanar` (                                                                                                                                                                         
  `kosziId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  `tanarId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  PRIMARY KEY (`kosziId`,`tanarId`),                                                                                                                                                                       
  CONSTRAINT `kosziIgazoloTanar_ibfk_1` FOREIGN KEY (`kosziId`) REFERENCES `koszi` (`kosziId`) ON DELETE CASCADE ON UPDATE CASCADE,                                                                        
  CONSTRAINT `kosziIgazoloTanar_ibfk_2` FOREIGN KEY (`tanarId`) REFERENCES `%INTEZMENYDB%`.`tanar` (`tanarId`) ON DELETE CASCADE ON UPDATE CASCADE                                                                  
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;                                                                                                                                            
CREATE TABLE  IF NOT EXISTS `kosziIgazoloOf` (                                                                                                                                                                            
  `kosziId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  `tanarId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  PRIMARY KEY (`kosziId`,`tanarId`),                                                                                                                                                                       
  CONSTRAINT `kosziIgazoloOf_ibfk_1` FOREIGN KEY (`kosziId`) REFERENCES `koszi` (`kosziId`) ON DELETE CASCADE ON UPDATE CASCADE,                                                                           
  CONSTRAINT `kosziIgazoloT_ibfk_2` FOREIGN KEY (`tanarId`) REFERENCES `%INTEZMENYDB%`.`tanar` (`tanarId`) ON DELETE CASCADE ON UPDATE CASCADE                                                                      
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;                                                                                                                                            
CREATE TABLE  IF NOT EXISTS `kosziDiak` (                                                                                                                                                                                 
  `kosziId` int(10) unsigned NOT NULL,                                                                                                                                                                     
  `diakId` int(10) unsigned NOT NULL,                                                                                                                                                                      
  `rogzitesDt` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,                                                                                                                                               
  `jovahagyasDt` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',                                                                                                                                         
  `kosziPontId` int(10) unsigned NOT NULL,                                                                                                                                                                 
  `pont` int(10) unsigned NOT NULL,                                                                                                                                                                        
  PRIMARY KEY (`kosziId`,`diakId`),                                                                                                                                                                        
  KEY `kosziDiak_ibfk_2` (`diakId`),                                                                                                                                                                       
  KEY `kosziDiak_ibfk_3` (`kosziPontId`),                                                                                                                                                                  
  CONSTRAINT `kosziDiak_ibfk_1` FOREIGN KEY (`kosziId`) REFERENCES `koszi` (`kosziId`) ON DELETE CASCADE ON UPDATE CASCADE,                                                                                
  CONSTRAINT `kosziDiak_ibfk_2` FOREIGN KEY (`diakId`) REFERENCES `%INTEZMENYDB%`.`diak` (`diakId`) ON DELETE CASCADE ON UPDATE CASCADE,                                                                   
  CONSTRAINT `kosziDiak_ibfk_3` FOREIGN KEY (`kosziPontId`) REFERENCES `%INTEZMENYDB%`.`kosziPont` (`kosziPontId`) ON DELETE CASCADE ON UPDATE CASCADE                                                     
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_hungarian_ci;