Update view using derived key in Database

Question

Is it possible to update this view by specifying the MessageTech and the txtmessageID? (txtmessageID and MessageId are the primary keys from the respective base tables.)

(SELECT
 txtmessageID,
 'S' AS MessageTech,
 MessageType,
 Message,
 MessageDate,
 MessageMobile,
 MessageRead,
 MessageSender
FROM
 MessageCentre)
UNION ALL
(SELECT
 MessageId,
 'E' AS MessageTech,
 Type,
 Text,
 Date,
 Address,
 [Read],
 Sender
FROM
 MessageCentreEmail)

Answer 1

The with some modification the view should be updateable, but not in its current form. https://msdn.microsoft.com/en-au/library/ms187956.aspx gives the rules for Partitioned Views

Some things you would need to change:

1. MessageTech column would need to be part of the underlying tables
2. MessageTech needs to be part of the primary key of each underlying table
3. MessageTech needs to have constraints on it in each table such that a given value could not possibly refer to more than one table e.g.

---

ALTER TABLE MessageCentre Add 
 MessageTech char(1) Not Null Default 'S',
 Constraint chk_MessageTechIs_S Check (MessageTech = 'S');

There are some other conditions too, which you can read about in that link, but those are the obvious changes that would need to occur.

• mendosi: Also dba.stackexchange.com/questions/58431/…