Category: MariaDB

In Postgres and a number of other databases you cannot directly set a columns’ default value to come from another table. Here I talk through how you can setup another table and use a trigger to pull the value from that table and insert it as a default value into a field of your choice if on Insert the value is null.

Postgres

-- Parent table
CREATE TABLE t002defaultvalues (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Stock table
CREATE TABLE t001stock (
    gid bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    warehouse text
);

-- Function to fetch default warehouse
CREATE OR REPLACE FUNCTION set_default_warehouse()
RETURNS trigger AS $$
BEGIN
    IF NEW.warehouse IS NULL THEN
        SELECT warehouse
        INTO NEW.warehouse
        FROM t002defaultvalues
        WHERE gid = 1;  -- adjust if you want another rule
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Trigger to apply the function
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
EXECUTE FUNCTION set_default_warehouse();

SQL Server (T-SQL)
In SQL Server, defaults can’t reference other tables, so an AFTER INSERT trigger updates only the newly inserted rows (via the inserted pseudo-table).

-- Parent table
CREATE TABLE dbo.t002defaultvalues (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX)  -- TEXT is deprecated in SQL Server
);

-- Stock table
CREATE TABLE dbo.t001stock (
    gid BIGINT IDENTITY(1,1) PRIMARY KEY,
    warehouse NVARCHAR(MAX) NULL
);
GO

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER dbo.trg_t001stock_default_warehouse
ON dbo.t001stock
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    UPDATE s
    SET s.warehouse = dv.warehouse
    FROM dbo.t001stock AS s
    INNER JOIN inserted AS i
        ON s.gid = i.gid
    CROSS APPLY (
        SELECT TOP (1) warehouse
        FROM dbo.t002defaultvalues
        WHERE gid = 1      -- adjust rule if needed
    ) AS dv
    WHERE s.warehouse IS NULL;
END;
GO

-- Example
INSERT INTO dbo.t002defaultvalues (warehouse) VALUES (N'Main Depot');
INSERT INTO dbo.t001stock (warehouse) VALUES (NULL); -- will be set by trigger

MariaDB
In MariaDB, a BEFORE INSERT trigger can assign NEW.warehouse from a scalar subquery.

-- Parent table
CREATE TABLE t002defaultvalues (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT
);

-- Stock table
CREATE TABLE t001stock (
    gid BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    warehouse TEXT NULL
);

DELIMITER $$

-- Trigger: fill warehouse from t002defaultvalues when NULL
CREATE TRIGGER trg_t001stock_default_warehouse
BEFORE INSERT ON t001stock
FOR EACH ROW
BEGIN
    IF NEW.warehouse IS NULL THEN
        SET NEW.warehouse = (
            SELECT warehouse
            FROM t002defaultvalues
            WHERE gid = 1    -- adjust rule if needed
            LIMIT 1
        );
    END IF;
END$$

DELIMITER ;

-- Example
INSERT INTO t002defaultvalues (warehouse) VALUES ('Main Depot');
INSERT INTO t001stock () VALUES (); -- will be set by trigger

Adjust WHERE gid = 1 to whatever selection rule you need (e.g., ORDER BY created_at DESC LIMIT 1, or WHERE active = 1 LIMIT 1).