wR5wYGXp

1SET FOREIGN_KEY_CHECKS=0;
2
3DROP TABLE IF EXISTS Sample CASCADE;
4DROP TABLE IF EXISTS LocationCode CASCADE;
5DROP TABLE IF EXISTS SampleTypeCode CASCADE;
6
7CREATE TABLE Sample (
8    sample VARCHAR(50) NOT NULL,
9    location VARCHAR(50),
10    sampleType VARCHAR(50),
11    PRIMARY KEY (sample)
12) ENGINE = MyISAM DEFAULT CHARACTER SET = latin1;
13
14
15CREATE TABLE LocationCode (
16    code VARCHAR(50) NOT NULL,
17    text VARCHAR(50),
18    sequence INTEGER,
19    PRIMARY KEY (code)
20) ENGINE = MyISAM DEFAULT CHARACTER SET = latin1;
21
22
23CREATE TABLE SampleTypeCode (
24    code VARCHAR(50) NOT NULL,
25    text TEXT,
26    sequence INTEGER,
27    PRIMARY KEY (code)
28) ENGINE = MyISAM DEFAULT CHARACTER SET = latin1;
29
30SET FOREIGN_KEY_CHECKS=1;
31
32ALTER TABLE Sample ADD CONSTRAINT
33    FOREIGN KEY (location) REFERENCES LocationCode (code);
34
35ALTER TABLE Sample ADD CONSTRAINT
36    FOREIGN KEY (sampleType) REFERENCES SampleTypeCode (code);
37	
38ALTER TABLE Sample DROP PRIMARY KEY;
39ALTER TABLE Sample DROP INDEX location;
40ALTER TABLE Sample DROP INDEX sampleType;
41
42DROP TABLE Sample;
43
44CREATE TABLE Sample (
45  sample VARCHAR(50) NOT NULL,
46  location VARCHAR(50),
47  sampleType VARCHAR(50),
48  PRIMARY KEY (sample)
49) ENGINE=MyISAM;
50
51CREATE INDEX location ON Sample (location ASC);
52CREATE INDEX sampleType ON Sample (sampleType ASC);
53ALTER TABLE Sample ADD PRIMARY KEY (sample);