开发者

Is it possible to insert a 'row' into a view composed of multiple tables with a common auto-increment primary key?

I've been reading up more about views in MySQL (not being my strongpoint), but I'm now wondering if anyone has an example of how to insert a row into a view composed of more than one table, where there is a supertype and subtype relationship?

For example, the 'party model' hierarchy, showing roles of employer and staff:

      party
    _ _ | _ _
   |         |
employer   staff

The tables for which (simplified):

party                             employer               staff
--------------------------------  ---------------------  -----------------
party_ID INT auto_increment (PK)  party_ID INT (FK)      party_ID INT (FK)
party_name VARCHAR                tax_reference VARHCAR  job_title VARCHAR
etc...                            etc...                 etc...

I know I can create a view to deal with just the relationship concerned:

CREATE VIEW staff_view AS SELECT party.party_ID, etc... FROM party INNER JOIN staff

When I add a member of staff, I want to create a record in the party table, then reference the auto-increment party_ID in t开发者_开发知识库he staff table. I can't see how to perform an insert in one statement, something like:

INSERT INTO staff_view (party_name, job_title, etc...) VALUES ('John Doe', 'CEO', etc)

I know I can acheive this programatically, but I am hoping there's a way to do it directly in the database and maintain the referential integrity. How would you approach this issue?

(MySQL documentation: http://dev.mysql.com/doc/refman/5.1/en/view-updatability.html)


Maybe you can solve your problem with a insert trigger?

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜