CODEWITHSUNDEEP
sqldatabasepostgresqlkey
Stefan Sprenger
Stefan Sprenger

Reputation: 1080

PostgreSQL Addition in Primary Key in Create Table

i'm facing a problem with a primary key in PostgreSQL, my plan was to make an addition of two values and set this to one primary key, how could this be realized (first try below):

/* Tabelle fuer die Test*/  
create table Test(
var_a integer,
var_b integer,
var_key integer,
var_key = var_a + var_b,
primarykey(var_key),
);

if i call this with a foreign key then it should be one value:

foreign key (var_key_f) references Test(var_key),

EDIT: I know th option of two multiple primary keys entries but i want to have only one primary key, so that i not have to reference over two vars again. I need to have both variables generated to one primary key.

Upvotes: 1

Views: 179

Answers (1)

wildplasser
wildplasser

Reputation: 44250

It appears this can be accomplished without triggers: (pg-9.3):

DROP SCHEMA tmp CASCADE;
CREATE SCHEMA tmp ;
SET search_path=tmp;

CREATE TABLE test
        ( var_key INTEGER NOT NULL PRIMARY KEY
        , var_a INTEGER NOT NULL
        , var_b INTEGER NOT NULL
        , var_key_f INTEGER REFERENCES test(var_key)
        , CONSTRAINT the_sum CHECK (var_a+var_b = var_key)
        );

INSERT INTO test(var_key, var_a, var_b) VALUES(42, 21, 21); -- Ok
INSERT INTO test(var_key, var_a, var_b) VALUES(666, 660, 6); -- Ok

INSERT INTO test(var_key, var_a, var_b) VALUES(34, 21, 11); -- bad sum
INSERT INTO test(var_key, var_a, var_b) VALUES(666, 600, 66); -- duplicate sum

INSERT INTO test(var_key, var_a, var_b, var_key_f) VALUES(14, 6, 8, 42); -- Ok
INSERT INTO test(var_key, var_a, var_b, var_key_f) VALUES(13, 5, 8, 43); -- Bad FK

Result:

NOTICE:  drop cascades to table tmp.test
DROP SCHEMA
CREATE SCHEMA
SET
CREATE TABLE
INSERT 0 1
INSERT 0 1
ERROR:  new row for relation "test" violates check constraint "the_sum"
DETAIL:  Failing row contains (34, 21, 11, null).
ERROR:  duplicate key value violates unique constraint "test_pkey"
DETAIL:  Key (var_key)=(666) already exists.
INSERT 0 1
ERROR:  insert or update on table "test" violates foreign key constraint "test_var_key_f_fkey"
DETAIL:  Key (var_key_f)=(43) is not present in table "test".

Upvotes: 1

Related Questions