From d817962ed43b7c3f4225626ac50942952461a33d Mon Sep 17 00:00:00 2001 From: "G. Richardson" Date: Sun, 7 Aug 2016 22:48:09 +0200 Subject: [PATCH] Tabelle prices - not null und unique auf parts_id und partsgroup_id --- SL/DB/MetaSetup/Price.pm | 6 ++++-- sql/Pg-upgrade2/prices_unique.sql | 9 +++++++++ 2 files changed, 13 insertions(+), 2 deletions(-) create mode 100644 sql/Pg-upgrade2/prices_unique.sql diff --git a/SL/DB/MetaSetup/Price.pm b/SL/DB/MetaSetup/Price.pm index 29c0de338..a0c26391f 100644 --- a/SL/DB/MetaSetup/Price.pm +++ b/SL/DB/MetaSetup/Price.pm @@ -10,13 +10,15 @@ __PACKAGE__->meta->table('prices'); __PACKAGE__->meta->columns( id => { type => 'serial', not_null => 1 }, - parts_id => { type => 'integer' }, + parts_id => { type => 'integer', not_null => 1 }, price => { type => 'numeric', precision => 15, scale => 5 }, - pricegroup_id => { type => 'integer' }, + pricegroup_id => { type => 'integer', not_null => 1 }, ); __PACKAGE__->meta->primary_key_columns([ 'id' ]); +__PACKAGE__->meta->unique_keys([ 'parts_id', 'pricegroup_id' ]); + __PACKAGE__->meta->foreign_keys( parts => { class => 'SL::DB::Part', diff --git a/sql/Pg-upgrade2/prices_unique.sql b/sql/Pg-upgrade2/prices_unique.sql new file mode 100644 index 000000000..f086283c9 --- /dev/null +++ b/sql/Pg-upgrade2/prices_unique.sql @@ -0,0 +1,9 @@ +-- @tag: prices_unique +-- @description: DB-Constraint - nur ein Preis pro Artikel pro Preisgruppe +-- @depends: release_3_4_1 + +-- it would be easier to just have a composite primary key on parts_id and +-- pricegroup_id, but that would need some code refactoring +ALTER TABLE prices ADD CONSTRAINT parts_id_pricegroup_id_unique UNIQUE (parts_id, pricegroup_id); +ALTER TABLE prices ALTER COLUMN parts_id SET NOT NULL; +ALTER TABLE prices ALTER COLUMN pricegroup_id SET NOT NULL; -- 2.20.1