CODEWITHSUNDEEP
sqloracle-databaseconstraintscreate-tabledefault-constraint
Light Infinite
Light Infinite

Reputation: 35

How to insert default contraint's value as a column value at the time of Table creation?

I am running ORACLE. And I am stuck with a problem. Can anyone tell me, how can I set a DEFAULT constraint's value as a column value from that same table during creation ? For example:

CREATE table testing
(
 id varchar2(20),
 name varchar2(20),
 myvalue varchar2(30) default id + name
)

In the above table, how can I set the myvalue's default constraint's value as the concatenated value of id and name ?? That is, I want the myvalue field's value to be the values of id and name fields when the table is being created OR using the CREATE table command ONLY. How can it be done?

Please help me out. Thank you.

Upvotes: 1

Views: 72

Answers (1)

Rahul Tripathi
Rahul Tripathi

Reputation: 172588

You can try to modify your table as:

ALTER TABLE testing
MODIFY (myvalue VARCHAR2(40) GENERATED ALWAYS AS (id || ' ' || name) VIRTUAL);

or you can try to create a TRIGGER like this:

CREATE OR REPLACE TRIGGER     myTrigger
BEFORE     INSERT OR UPDATE
ON     testing
FOR EACH ROW
BEGIN
     :NEW.myvalue:= :NEW.id
                || ' '
                || :NEW.name;
END;

Upvotes: 4

Related Questions