Hibernate, SQL Server: How could I use default value as value from another column

Question

For example: I have COLUMN_PK column of type int identity(1,1) and column COLUMN_NUM pf type int, how could I define default value for COLUMN_NUM - value of COLUMN_PK?

So if I have entity with not specified COLUMN_NUM it will be populated with generated value from COLUMN_PK. But if COLUMN_NUM is specified its value will be used.

Answer 1

Use computed column and a scalar function as default value.

Answer 2

Create a 3rd column that is computed

CREATE TABLE MyTable (
   COLUMN_PK int NOT NULL identity(1,1) ,
   ...
   COLUMN_NUM_internal int NULL,
   COLUMN_NUM AS COALESCE (COLUMN_NUM_internal, COLUMN_PK),
   ...
)

The PK value is not known until INSERT ( of course). But not before so you have do something like this or use a trigger to update COLUMN_NUM. However, this solution above works for subsequent UPDATEs too with no extra code (i.e. another trigger for UPDATE)

Answer 3

I would enforce this through a trigger.

create trigger tr_IU_YourTable on YourTable
for insert,update
as
begin
    update yt
        set column_num = yt.column_pk
        from inserted i
            inner join YourTable yt
                on i.column_pk = yt.column_pk
                    and yt.column_num is null
end
go

Answer 4

How about having a child table to store just COLUMN_PK and COLUMN_NUM? This table will have a record only if COLUMN_NUM value is specified. Then you can do:

CREATE TABLE Parent (COLUMN_PK int NOT NULL identity(1,1), someCol int NOT NULL)
CREATE TABLE Child  (COLUMN_PK int NOT NULL, COLUMN_NUM int NOT NULL)

INSERT INTO Parent (someCol) VALUES (1)
INSERT INTO Parent (someCol) VALUES (2)
INSERT INTO Parent (someCol) VALUES (3)
INSERT INTO Parent (someCol) VALUES (4)
INSERT INTO Parent (someCol) VALUES (5)
INSERT INTO Child VALUES (1, 10)
INSERT INTO Child VALUES (3, 30)
INSERT INTO Child VALUES (5, 50)

SELECT COLUMN_PK,
       CASE WHEN EXISTS (SELECT NULL FROM Child WHERE Child.COLUMN_PK = Parent.COLUMN_PK)
            THEN (SELECT COLUMN_NUM FROM Child WHERE Child.COLUMN_PK = Parent.COLUMN_PK)
            ELSE COLUMN_PK
       END
FROM Parent

Answer 5

Using NHibernate to call a stored procedure to insert data, rather than use a direct insert via a .save method could give you more control.