Can the default value of one column be another column in SQL?

user_13325358 0
Viewed 257

If no value is entered in the description column in the product table, I want it to be equal to that in the product_name column by default. I tried it as follows but it didn't work. Is this possible?

CONSTRAINT def_desc DEFAULT product.product_name FOR description;

sql-server sql
3 Answers

A common solution is to create a view for displaying the data. In the view you compute the description. Like this

CREATE TABLE t
(name VARCHAR2(10),
 description VARCHAR2(10));
 
CREATE OR REPLACE VIEW t_v (name, description)
AS 
SELECT name, NVL(description, name) FROM t;

INSERT into t (name, description) VALUES ('CAR','cool car');
INSERT into t (name, description) VALUES ('BIKE',NULL);

SELECT * FROM t_v;

NAME       DESCRIPTIO
---------- ----------
CAR        cool car  
BIKE       BIKE
user_41898140

No. The default value can be a constant or (in general) a non-volatile function. That is, a function value that doesn't change each time you call it.

Some databases such as SQL Server allow any function.

The logical issue with using another column is how to handle the value when the referenced column changes. If you run:

create table t (
     x int,
     y int default x
);

insert into t (x) values (1);

update t set x = 2;

Then does y have a value of 1 or 2? If you want y to be 2, then you can use a computed column.

user_11440350

If your question is to use only default, then no. If you want the column to be not empty, you can always have a constraint.

If the scenario you are facing doesn't allow you to make the field mandatory, then you can use a trigger. It updates just the way you want.

user_57983000
Related