Can you use an inserted variable in a default constraint

Question

Working in SQL Server, let's say I have a database with two tables: events and competitors.

events has the following columns:

[event_id] [event_name] [winner_id] [loser_id]

competitors has the following columns:

[comp_id] [comp_name]

I wanted to add a default constraint to [event_name] to save me time when inserting a new event. Something similar to:

ALTER TABLE [events]
ADD CONSTRAINT DF_event_name
DEFAULT CAST([winner_id] AS varchar(10)) + ' vs ' + CAST([loser_id] AS varchar(10)) FOR [event_name]

Of course, this is a simplified version, it would also need to implement a join statement with competitors ON comp_id = winner_id and comp_id = loser_id.

When I tried setting the above default constraint, I got the response you are probably expecting:

The name "winner_id" is not permitted in this context. Valid expressions are constants, constant expressions, and (in some contexts) variables. Column names are not permitted.

So is there any way to work around what I'm trying to do?
Is there any way to automatically set the value of a cell to a string implementing data inserted from the same row? If that's possible, can it also be done using a join statement? My next idea would be a trigger.

Answer 1

No, default constraints cannot depend on other columns. Further, neither default constraints nor computed columns can reference other tables (unless you define a scalar udf - not recommended!). As mentioned, you can achieve this constraint using triggers.

I'd recommend you create and use a view over these two tables. Select from the tables joined together and compute the data you want.

Answer 2

Yes, there is a few ways to do it.

Computed Column

In simplified version, if you need only ids from the events table, you can use computed column.

Something like this:

CREATE TABLE events (
    event_id INT,
    winer_id INT,
    loser_id INT,
    event_name AS CAST(winer_id AS VARCHAR(16)) + ' vs ' + CAST(loser_id AS VARCHAR(16))
)

Triggers

But if you have to insert names from the other table you can use INSTEAD OF INSERT and INSTEAD OF UPDATE triggers.

Example below:

CREATE TRIGGER events1_insteadOfInsert ON events2
INSTEAD OF INSERT
AS
    INSERT INTO events
    SELECT event_id,winer_id,loser_id,c_win.comp_name+ ' vs ' + c_loss.comp_name
    FROM inserted i
    JOIN competitors c_win ON i.winer_id=c_win.comp_id
    JOIN competitors c_loss ON i.winer_id=c_loss.comp_id

View

The last option could be create a view to display result as you want. In this scenario you don't need event_name column in the definition of the table.

Example view:

CREATE VIEW vw_events
AS
    SELECT event_id,winer_id,loser_id,c_win.comp_name+ ' vs ' + c_loss.comp_name AS event_name
    FROM events e
    JOIN competitors c_win ON e.winer_id=c_win.comp_id
    JOIN competitors c_loss ON e.winer_id=c_loss.comp_id

In this case view looks like the best option.