yUC3FYhx

1DROP TABLE IF EXISTS asiakas;
2CREATE TABLE IF NOT EXISTS asiakas (
3ASIAKAS_ID int(11) NOT NULL UNIQUE AUTO_INCREMENT,
4  NIMI varchar(50) CHARACTER SET latin1 NOT NULL,
5  SALASANA varchar(50) CHARACTER SET latin1 NOT NULL,
6  OSOITE varchar(50) CHARACTER SET latin1,
7  POSTINRO varchar(5) CHARACTER SET latin1,
8  POSTITMP varchar(50) CHARACTER SET latin1,
9  PRIMARY KEY (`ASIAKAS_ID`)
10) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
11 
12DROP TABLE IF EXISTS kategoria;
13CREATE TABLE IF NOT EXISTS kategoria(
14  KATEGORIA varchar(50) CHARACTER SET latin1 NOT NULL UNIQUE,
15  PRIMARY KEY (`KATEGORIA`)
16) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
17 
18DROP TABLE IF EXISTS laite;
19CREATE TABLE IF NOT EXISTS laite(
20  SARJANUMERO int(11) NOT NULL UNIQUE AUTO_INCREMENT,
21  NIMI varchar(50) CHARACTER SET latin1 NOT NULL,
22  MALLI varchar(50) CHARACTER  SET latin1 NOT NULL,
23  MERKKI varchar(50) CHARACTER  SET latin1 NOT NULL,
24  SIJAINTI varchar(50) CHARACTER  SET latin1 NOT NULL,
25  KUVAUS varchar(50) CHARACTER  SET latin1 NOT NULL,
26  KATEGORIA varchar(50) CHARACTER SET latin1 NOT NULL,
27  OMISTAJA varchar(50) CHARACTER SET latin1 NOT NULL,
28  PRIMARY KEY (`SARJANUMERO`)
29) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
30 
31DROP TABLE IF EXISTS status;
32CREATE TABLE IF NOT EXISTS status (
33  STATUS_ID int(11) NOT NULL UNIQUE AUTO_INCREMENT,
34  STATUS_NIMI varchar(50) CHARACTER SET latin1 NOT NULL,
35  PRIMARY KEY (`STATUS_ID`)
36) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
37 
38DROP TABLE IF EXISTS varaus;
39CREATE TABLE IF NOT EXISTS varaus (
40  VARAUS_ID int(11) NOT NULL UNIQUE AUTO_INCREMENT,
41  SARJANUMERO int(11) NOT NULL,
42  ALKUPVM date NOT NULL,
43  LOPPUPVM date NOT NULL,
44  STATUS_ID int(11) NOT NULL,
45  ASIAKAS_ID int(11) NOT NULL,
46  PRIMARY KEY (`SARJANUMERO`)
47) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
48 
49ALTER TABLE varaus
50ADD CONSTRAINT FK_laite
51    FOREIGN KEY (SARJANUMERO) REFERENCES laite (SARJANUMERO),
52 
53ADD CONSTRAINT FK_asiakas
54    FOREIGN KEY (ASIAKAS_ID) REFERENCES asiakas (ASIAKAS_ID),
55 
56ADD CONSTRAINT FK_status
57    FOREIGN KEY (STATUS_ID) REFERENCES status (STATUS_ID);
58   
59ALTER TABLE laite
60ADD CONSTRAINT FK_laitekategoria
61    FOREIGN KEY (KATEGORIA) REFERENCES kategoria (KATEGORIA);