efxmhs10

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) 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  FOREIGN KEY (KATEGORIA) REFERENCES kategoria(KATEGORIA)
30) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
31 
32DROP TABLE IF EXISTS status;
33CREATE TABLE IF NOT EXISTS status (
34  STATUS_ID int(11) NOT NULL UNIQUE AUTO_INCREMENT,
35  STATUS_NIMI varchar(50) CHARACTER SET latin1 NOT NULL,
36  PRIMARY KEY (`STATUS_ID`)
37) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
38 
39DROP TABLE IF EXISTS varaus;
40CREATE TABLE IF NOT EXISTS varaus (
41  VARAUS_ID int(11) NOT NULL UNIQUE AUTO_INCREMENT,
42  SARJANUMERO int(11) NOT NULL,
43  ALKUPVM date NOT NULL,
44  LOPPUPVM date NOT NULL,
45  STATUS_ID int(11) NOT NULL,
46  ASIAKAS_ID int(11) NOT NULL,
47  PRIMARY KEY (`SARJANUMERO`)
48) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8 COLLATE=utf8_swedish_ci;
49 
50ALTER TABLE varaus
51ADD CONSTRAINT FK_laite
52    FOREIGN KEY (SARJANUMERO) REFERENCES laite (SARJANUMERO),
53
54ADD CONSTRAINT FK_asiakas
55    FOREIGN KEY (ASIAKAS_ID) REFERENCES asiakas (ASIAKAS_ID),
56
57ADD CONSTRAINT FK_status
58    FOREIGN KEY (STATUS_ID) REFERENCES status (STATUS_ID);