1

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?

2
  • FKs, DEFAULTs, etc, have significant limitations. SQL is not a full programming language. See if a TRIGGER will help you implement what you want. If not, you are stuck with using app code or reformulating to use Stored routines.
    – Rick James
    Dec 18, 2022 at 16:20
  • Problem is that SQL triggers act on all rows on the concerned table, so I don‘t see how to use a trigger to act on the first inserted row of an execution set only, no ?
    – DevelJoe
    Dec 18, 2022 at 17:12

1 Answer 1

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.

8
  • Thanks for that, sir! The only complication is that the auto_incremented PK generated must be the FK from table_2, referencing table_1. And to my understanding, I cannot insert the record into table 1 first, as it needs to have an ID to refer to from table 1 upon insert. No? I‘ll test a solution anf will post it later if it works properly.
    – DevelJoe
    Dec 18, 2022 at 19:23
  • Well it seems my table creations already fail actually. When I run the last statements posted in my question; I get: #1005 - Cannot create table (Error: 150 "Foreign key constraint is incorrectly formed"); any idea?
    – DevelJoe
    Dec 18, 2022 at 20:05
  • Looks like the problem was due to the fact that I did not index the test_value field in the query. At least when I did index it, I could set the FOREIGN KEY without any problem. Didn't MariaDB automatically index fields referenced by foreign keys?
    – DevelJoe
    Dec 18, 2022 at 20:24
  • Err 150 -- Either do the other CREATE first; or add the FKs after both CREATEsa are in place.
    – Rick James
    Dec 18, 2022 at 22:29
  • As for the PKs -- please explain the purpose of the PKs, why they need to be auto_inc, etc.
    – Rick James
    Dec 18, 2022 at 22:31

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.