Setting default value using data from another table

Question

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?

I think you have a confused expectation about foreign keys. When you insert a record to Test_1 a new record in Test_2 IS NOT automatically created. You can insert a few records into Test_1. When it is time to insert records into Test_2 what value to you want to select from A1? Foreign key does not mean one-to-one relationship — cha
– cha, Commented Apr 28, 2015 at 1:52
@cha, thanks for the comment. I assume that the records in Test_1 are already there, e.g. they may be some constants for a certain column/field in Test_2. I merely want to use one of the constants defined in Test_1 as the default value for the corresponding column in Test_2 table. — MLister
– MLister, Commented Apr 28, 2015 at 5:07
@cha, please see edit above, where I give an example to clarify the context. — MLister
– MLister, Commented Apr 28, 2015 at 5:15

cha · Accepted Answer · 2015-04-28 05:31:55Z

3

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

answered Apr 28, 2015 at 5:31

cha

10.4k1 gold badge20 silver badges27 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MLister Over a year ago

How is the performance of triggers? Does using it impact the performance of database in any way?

cha Over a year ago

I think if you don't overdo with them they should be fine. In sqlite triggers are especially fast as the only statement that is allowed there is one of update/insert/delete/select. You can't really put a lot of code there. So, you will not be able to slow down your system significantly

Collectives™ on Stack Overflow

Setting default value using data from another table

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related