when creating table in sqlite, want define default value of column using value table (as column foreign key). this:
create table test_1(a1 integer primary key, b1 text not null); create table test_2(a2 integer primary key, b2 integer not null default [a value in column a1 of test_1]); i assume test_1 populated records provides possible values b2 in test_2 table. example, these may constants 'created', 'in progress', 'paused', 'finished' 'status' column (i.e. b2).
i can of course pick primary key of value test_1 , hard code default value b2, error prone if change data of test_1 later. @ same time, cannot use select statement inside create.
if not feasible in sqlite, possible other sql engines? or more generally, should enforced application logic rather database design?
i think can use trigger
you can test value of b2 on insert , if not populated can select value in column a1 of test_1 , populate column:
create trigger if not exists test_2_b2_value after insert on test_2 each row when test_2.b2 null begin new.b2 = (select a1 test_1 limit 1); end 