Set Default Value of Column Equal to PK of inserted record, and use it as Foreign Key Reference

Question

I'm using MariaDB 10.3, and what I'm trying to do is an establishment of a 1-to-n relationship as for example specified here in the section "One-To-Many-Relationship". I'm trying to do so via:

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL DEFAULT ID
);

CREATE TABLE related_table (
  ID INT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
)

The SQL Fiddle Editor etc. fail at the execution of this, with the Error:

Function or expression 'AUTO_INCREMENT' cannot be used in the DEFAULT clause of 'ID'

So I suppose you cannot set the default value of a column equal to another column, if the value of that other column is defined by a function or an expression?

If I then omit the AUTO_INCREMENT, I get:

Can't create table "fiddle"."related_table" (errno: 150 "Foreign key constraint is incorrectly formed")

So I assume you cannot create FOREIGN KEY constraints based on referenced columns with a default value?

Do you see another possibility to do what I need, which is:

A) When I insert a new record into test_table and do not provide test_value, its value should be equal to the primary key of the inserted record, which should be auto-generated.

B) The related_table's ID should refer to the test_table's test_value as foreign key.

The only workaround I currently see is to:

CREATE TABLE test_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  test_value INT NOT NULL
);

CREATE TABLE related_table (
  ID INT PRIMARY KEY AUTO_INCREMENT NOT NULL,
  message VARCHAR(10) NOT NULL DEFAULT 'hello',
  FOREIGN KEY (ID) REFERENCES test_table(test_value)
  ON DELETE CASCADE
  ON UPDATE RESTRICT
);

And then, if I insert for example 3 records into test_table:

1. Insert first new record into test_table, and grab the LAST_INSERT_ID.
2. Update the first record just created by setting the value of test_value of it equal to that returned LAST_INSERT_ID.
3. Insert the two remaining records into test_table, this time directly with the LAST_INSERT_ID returned from before as the value of test_value.
4. Insert new record into related_table with the value of the returned LAST_INSERT_ID as its primary key ID field.

But this seems overcomplicated. Isn't there a better solution for this usecase?

Answer 1

For your 3-row example, redesign thus:

• Have 2 tables

• One table has one row per clump and provides the auto_increment

• The other table has the 3 rows

• Then build a transaction to do all the steps:

  BEGIN;
  INSERT INTO table1 ...;
  get $id from the LAST_INSERT_ID()
  foreach ...   -- walk through the 3 things
      INSERT INTO table2
          (..., test_value, ...)
          VALUES (..., $id, ...);
  COMMIT;
  

Think through which columns 'belong' in one table versus the other table. You will probably find the end result is cleaner than the design you hypothecated.