CODEWITHSUNDEEP
sqlsqlite
MLister
MLister

Reputation: 10310

Setting default value using data from another table

When creating a table in SQLite, I want to define the default value of a column using value from another table (as this column is a foreign key). Something like 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 that Test_1 is already populated with the records that provides possible values for B2 in Test_2 table. For example, these may be some constants like 'Created', 'In Progress', 'Paused', 'Finished' for a 'Status' column (i.e. B2).

I can of course pick the primary key of a value from Test_1 and hard code it as the default value for B2, but that is error prone if I change the data of Test_1 later. At the same time, we cannot use SELECT statement inside a CREATE.

If this is not feasible in SQLite, is it possible for other SQL engines? Or more generally, is this something that should be enforced by application logic rather than by database design?

Upvotes: 4

Views: 5511

Answers (1)

cha
cha

Reputation: 10411

I think you can use a TRIGGER

You can test for the value of B2 on INSERT and if it is not populated then you can select a value in column A1 of Test_1 and populate the column:

CREATE TRIGGER IF NOT EXISTS Test_2_B2_value
AFTER INSERT ON Test_2
FOR EACH ROW WHEN Test_2.B2 IS NULL
BEGIN 
NEW.B2 = (SELECT A1 FROM Test_1 LIMIT 1);
END

Upvotes: 3

Related Questions