1 -- @tag: schema_normalization_1
2 -- @description: Datenbankschema Normalisierungen
3 -- @depends: release_2_6_1
6 CREATE SEQUENCE assembly_assembly_id_seq;
7 ALTER TABLE assembly ADD COLUMN assembly_id INTEGER;
8 UPDATE assembly SET assembly_id = nextval('assembly_assembly_id_seq');
9 ALTER TABLE assembly ADD PRIMARY KEY( assembly_id );
10 ALTER TABLE assembly ALTER assembly_id SET DEFAULT nextval('assembly_assembly_id_seq');
13 ALTER TABLE shipto ALTER COLUMN shipto_id SET NOT NULL;
14 ALTER TABLE shipto ADD PRIMARY KEY (shipto_id);
17 --ALTER TABLE oe ADD FOREIGN KEY (customer_id) REFERENCES customer (id);
18 --ALTER TABLE oe ADD FOREIGN KEY (vendor_id) REFERENCES vendor (id);
20 -- orderitems_primary_key
21 ALTER TABLE orderitems ADD PRIMARY KEY (id);
24 UPDATE parts SET unit = 'Stck' WHERE unit IS NULL;
25 ALTER TABLE parts ALTER COLUMN unit SET NOT NULL;
27 -- makemodel_id_column
28 ALTER TABLE makemodel ADD COLUMN tmp integer;
29 UPDATE makemodel SET tmp = make::integer WHERE COALESCE(make, '') <> '';
30 ALTER TABLE makemodel DROP COLUMN make;
31 ALTER TABLE makemodel RENAME COLUMN tmp TO make;
33 CREATE SEQUENCE makemodel_id_seq;
34 ALTER TABLE makemodel ADD COLUMN id integer;
35 ALTER TABLE makemodel ALTER COLUMN id SET DEFAULT nextval('makemodel_id_seq');
36 UPDATE makemodel SET id = nextval('makemodel_id_seq');
37 ALTER TABLE makemodel ALTER COLUMN id SET NOT NULL;
38 ALTER TABLE makemodel ADD PRIMARY KEY (id);