View and UPDATE

More detail.

> On Wednesday, 18 February 2015 20:56:00 UTC+11, Derek Asirvadem wrote:
>
> > SQL can't constrain views in any way, can't use views as a FK target.
>
> ???
>
> The View is in fact a SELECT statement.
>
> 1. While it is very silly to implement views of views, and views of views of views, it is not illegal. You must be using a non-SQL.
>
> [a] Very Silly. Theoreticians often do not differentiate between base relations (tables) and derived relations (views). That is a huge impediment, and it poses a problem everywhere, here as well.
> [b] When you implement in implementation land, you cannot remain in the freedom of abstraction in theory land. That will trip you up.
> [c] The fact that you are writing SQL means you are stepping out of fiction land, and into implementation land. Deal with it, or do not take the job.
> [d] Get your head around the notion of a single-level view, ie. that uses tables only, not views, and your problem will disappear.
>
> 2. If "Constraining" means stating a set of conditions, then "constraining" a View is legal. You must be using a non-SQL.
>
> 3. In any case, even if you are using a non-SQL, to overcome that obstacle, simply write a single-level view, with the "constraint" in the WHERE clause.

4. The reason you can't use a View as an FK "target" is a good one. Same as:

>>>
We let brilliant but impotent theoreticians create their absurd SELECTS, and thresh the data 50 ways to Sunday. But we don't let them inflict that insanity on the rest of the users. Ie. We do not let them *create a View* from absurd SELECTs, such as:

- duplicate column names
- establish a Primary or Unique Key on a View
- other, similar forms of insanity

All of which are beloved by theoreticians who can't tell the difference between base relationa (tables in implementation land) and derived relations (the result of a SELECT, including View). Refer [a] above.

MS with their very very marvellous humaniversity and 500 professors, have gone the whole hog (ie. piggery): they give you, ta da, The Materialised View. The ultimate in a single self-contradicting term. <<<<

Let's see now, Normalisation; PKs; FKs, apply only to base relations (tables). Not to derived relations (SELECTs, Views).

Let's see now, views are unstable, they disappear and reappear as often as theoreticians change their views (sorry), I mean their relations (sorry), I mean their shorts. And tables are a bit more stable, especially if we do not GRANT anything but SELECT to the theoreticians.

So for the reasons given above, since the notion of an FK on a base relation (table) referencing an ever-changing View, is totally unsound (except to the person who changes it everyday), we disallow it.

> > Just look at the butt-ugly porcine ungainliness of UPDATE. Updating
> > one table from another has to be the most cumbersome, verbose, and
> > redundant aspect of SQL, not that it lacks competition (except in any
> > other language). Why can we not say instead
> >
> > R(a, b, c) = S(a, b, c) WHERE R.x = S.x
>
> Because SQL does not parse Swahili, or Urdu, or RADBL, no one stated that it could, silly to expect bread from the butcher.
>
> If you translated that giberish into recognisable operations, it is very easy to code in SQL.
>
> UPDATE is cumbersome only if you do not understand that:
> - the target is a single table
> - the FROM should be a full SELECT.
>

I think you know:

- SQL is Structured *QUERY* Language
- When Chamberlain and Boyce wrote System R, there was no INSERT, UPDATE or DELETE
- All writes were via CICS/TCP, through the back door
- which was of course incomplete, so when they had to package it for market, as SQL, that had to be added, to a genuine Query language that had no write verbs

Therefore consider:
- the porcine ungainliness is only if one does not appreciate that DELETE and UPDATE are the target of a SELECT, a pipe so to speak

And to avoid "difficulty":
- forget the UPDATE, write the SELECT, and only the SELECT for the target rows - then pipe it into the FROM of the UPDATE

Ok, there is a fair amount of reason coming out in these answers, that has a value. I have to concede, they are not stupid questions, they are simply the questions of a novice. Unfortunately tainted with a huge bias for fictitious capabilities of fictional languages, learned bias.

Cheers
Derek Received on Wed Feb 18 2015 - 16:13:56 CET