Level Up Your MariaDB Tables: Leveraging Functions for Default Values

Here's a breakdown:

• Before MariaDB 10.2: Limited to constants as default values.
• After MariaDB 10.2: You can use functions and expressions along with constants in the DEFAULT clause.

For instance, you can set a default value for a column to the current year using the YEAR(NOW()) function. This ensures the column always has the year of insertion.

There's a caveat though. Some functions, like user-defined functions, might not be suitable for default values due to performance considerations.

CREATE TABLE users (
  id INT AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(50) NOT NULL,
  registered_year YEAR DEFAULT (YEAR(NOW()))
);

This code creates a table named "users" with three columns:

• id: An auto-incrementing integer as the primary key.
• username: A string value to store usernames (not null).
• registered_year: A year value to store the registration year. The default for this column is set using the YEAR(NOW()) function, which extracts the year from the current date and time.

Setting Default with User:

CREATE TABLE products (
  product_id INT AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(255) NOT NULL,
  created_by VARCHAR(50) DEFAULT (USER())
);

• created_by: A string value to store the username of the user who created the product record. The default for this column is set using the USER() function, which returns the username of the currently logged-in user.

CREATE TABLE subscriptions (
  subscriber_id INT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(100) NOT NULL UNIQUE,
  valid_until DATE DEFAULT (NOW() + INTERVAL 1 YEAR)
);

• email: A string value to store subscriber emails (not null and unique).
• valid_until: A date value to store the subscription validity. The default for this column is set using an expression that combines NOW() with an interval of 1 year. This ensures the subscription is valid for one year from the creation date.

• Triggers are stored procedures that automatically execute in response to specific events on a table, like inserts or updates.
• You can create a trigger that fires on INSERT events and sets the desired value for the column before the row is inserted.

Stored Procedures:

• Stored procedures are pre-written SQL statements that can be executed by calling them with specific parameters.
• You can create a stored procedure that calculates the desired default value and then use it within your application logic to insert the value into the column.

Views:

• Views are virtual tables based on queries involving one or more underlying tables.
• You can define a view that includes a calculation for the desired default value and then insert data into the view instead of the actual table.

Client-Side Logic:

• In some cases, you can implement the logic to generate the default value within your application code before sending the data to the database.
• This approach can be useful for calculations specific to your application logic.

Choosing the Right Method:

The best method depends on your specific needs. Here's a general guideline:

• Use functions in the DEFAULT clause (MariaDB 10.2+) for simple calculations directly within the database.
• Use triggers for complex logic that needs to react to database events.
• Use stored procedures for reusable logic that might involve calculations or data manipulation beyond setting defaults.
• Consider views for presenting data with a specific format or including calculations.
• Use client-side logic for application-specific calculations before interacting with the database.