This SQL is mainly used for cascading query of menus. Giving a parent node can find out all the child nodes. And sub contact sub contact, a check to the end, very practical. However, this program can only be used in Oracle. When I find it, I will post it to share with you. I retested it again and explained it again.
Take a menu of windows system as an example. My watch menu like this. If I want to know which submenus are available under the file menu. I can use this SQL program like this:. Of course, the actual application will not be so simple, such as attaching actual conditions, especially permission management. At this time, according to your system requirements, whether to verify each or role, put the ID of these people in the field of validate to form a string, and N IDs are separated by commas.
Oracle has no action similar to restrict , cascade and set null in the deletion of foreign keys. Each one has an OrgID. OrgID is the primary key, and my response to the statement "Organization so-and-so just changed their OrgID to X" is always no they didn't unless they've suddenly become a new org. OrgID identifies them across time. An org can close its doors, and two years later re-open them again as the same orgid.
No problem. But if they suddenly decide they don't like their name or their identifier, they have to formally close the old org and open the new one. Thanks billy halim, August 08, - pm UTC. Tom, I impress and am very happy with your answer. It reflects your truly experiences about what best practice really is. For New England's Bill, from my last comment if you read it once again not all my paragraphs appoint to Tom.
I'm not tongue in cheek but I'm with tongue in check to someone whom just hit and run. Bill S. Sorry, Billy, but if you read my comment you'd see I wasn't referring to you.
I have read ALL of the comments - my response was why is everyone so adamant about updating primary keys? Everyone - NOT just you. I don't hit and run, either. I'm always here even if I'm not commenting. But thanks anyway. In some sense, I admire Tom Kyte. I can still admire someone despite great disagreement and some flame wars. If Tom really meant what he said, I admire Tom even more. Now, for this stupid Billy who has no idea about database design.
Have you ever understood that cascaded update can be applied to non-primary key? If you never did it, and you don't really understand unique key, never read books on database design especially by C. Date , you better shut your mouth. Yes, I am stupid but.. If you have any idea or examples, you can share with us like New England's Bill's. I appreciate other ideas and opinions.
Bernaridho is right - and so is Tom! As so often in discussions about databases, there is a dramatic clash between people talking about relational database theory , and people talking about vendor-specific DBMS best practice. Bernaridho is correct: nothing in relational database design precludes primary keys being updateable. Tom is also quite correct to say it isn't a good idea in practice though saying "if it is updateable, it isn't a primary key" isn't technically correct.
August 12, - am UTC. I have to admit I'm a person who almost automatically puts a sequence in as a primary key -- there are just too many cases where using the "natural" key would be too messy and impractical. For example, I work with a database that provides income support to families.
We use a sequence number for people. Maybe name, date of birth, gender and mother's name would be enough to provide a natural key, but then you are talking six columns assuming two for each name to identify a person -- so six columns on each payment row, etc. Not a good idea. Why the mother's name? Well, the children's names change. Without the mother's name, you have the distinct possibility of having two "BABY BOY"s with the same birth date, so not a unique key, even if you lived in a small enough place where the "real" name and birth date should provide a unique identifier.
Not to mention data entry error etc. And then maybe the mother decides to change her name? And in many other cases there's just no advantage to using a natural key: This is a payment for request some made on date some date to person some person. The three key columns are sort of meaningless, so why not just a single meaningless number instead?
Maybe name, date of birth, gender and mother's name I agree with the horrendous 6-column PK. It makes otherwise-simple-to-write table joins into a messy maintenance nightmare!
There are other additional positive side-effects from using a surrogate PK outside the scope of a cascade update. It can make for easier coding for the developer under certain circumstances. The sequence no. Personally, I had hoped when I first saw it in the docs. August 13, - am UTC. Never use a natural join without actual "on" conditions A reader, October 01, - am UTC. I have worked in a few companies where we use SSN's as a primary key- they are unique and they used as foreign keys in other tables containing tens of thousands of people and are a natural choice short of creating a user ID, which isn't possible due to the extent of the records and the interaction between other systems, paper forms, etc.
We would have at least a few SSN change request each week and if we didn't have the ability to change those entries quickly and easily it would be a huge waste of time. I think that in practice you should try to limit cascade on updates but it is sometimes a necessary evil that saves time and energy. But it shouldn't be a feature that is denied to the designer. The earlier person had a point that you didn't address which was that there is a difference between imperative and declarative integrity and Oracle should at least provide support for it.
I mean, its not good programming practice to test an integer if it is true or false but most languages support that kind of casting as an analogy. I guess. October 29, - am UTC. I have worked in a few companies where we use SSN's as a primary key SSN and other identifying data like that must be obscured from testers and developers, encrypted in many cases on disk, etc.
This has been true forever. If you believe they are a natural choice for a userid, we need to revisit that thought. You would update a primary key of a record that no longer exists? I don't get it. If the supervisor leaves assuming this is an employee database - well, their record would not leave, retention periods of years mandatory for that kind of stuff. So, you would NOT be updating their primary key would you answer: no, that was a rhetorical question completely.
I will not apologize for the bad design decision to use sensitive information that must be protected hence you want it in as FEW places as possible, not scattered all over the place and that changes frequently as you pointed out as a primary key.
You missed the design boat as they say. When will the cascade update be finished? Russ, November 02, - pm UTC. Like so many proponents of DB theory, Bernaridho would like to change the world for the better. I can only imagine his or her boss watching over their work asking, "when will this Primary Key cascade update be finished", and the response of, "the system cant do what i want it to do" and "you need to rethink your entire information strategy.
Real change arrives from innovation - think laterally and change your design of primary keys! Yes, but A reader, December 20, - pm UTC. I sympathize with Evan. The thing about sensitive data is an invalid criticism - SSN is just an example. First, with large external data sources, it can be a nightmare having generated a surrogate key to have to keep matching new data sets to it. I have done this and had cause to regret it, even though it was "best practice.
Second, you don't always have the choice of redesigning the schema. Even if it's your schema and you're in complete control of it with no developers affected, there's not always the time. This should be obvious. Oracle offers many features which can support bad practices, but which are standard and which people rely on.
This would be a prime case for adding another one; it would save a lot of people a lot of time and headache. December 20, - pm UTC. Did you know that exposing the SSN as a primary key is a security issue. Did you know that SSN's are not immutable - they are not candidates for primary keys.
I don't like the term 'best practice' myself - there is no such thing as a universal 'best practice'. If there was, it would be hard coded into the software " It took me a long time to read the whole discussion that has been going for 8 years now.
Funny how oracle has not come with the feature since the first question was asked. I am coming from SQL server and I googled into your discussion asking the same question. Soon after the key record is created, piles of data are referencing that record. And then, somebody finds out that the book ID is wrong because actually it belongs to a different property or whatever. But who cares. As long as we the ability to change the primary key and let the database change all records in all tables that has that foreign key with update cascade.
I do believe that eventually Oracle will support this feature. So we need to disable ar defer Fk to have ability to chenge user type. It would be nice to have "on update cascade". May 06, - am UTC. As we us "on delete cascade" same way can we use "on delete update ". As we use "on delete cascade" same way can we use "on updade cascade". February 09, - am UTC. A reader, March 20, - pm UTC. Tom, to preface, your responses and the information you've provided over the years on this site has been incredibly helpful for me.
I've learned more than I'd ever need to know about Oracle internals merely by reading through the numerous questions here. I find your responses to typically be accurate, direct, and on the ball. However, I occasionally find that some of your answers are not as forthright, especially regarding features and functionality that Oracle lacks, and this is one such case.
Just because one possible use of a foreign key is to reference a PRIMARY key does not mean that it is the only possible use of a foreign key. I fail to see how your objection would be valid in the context of updating columns specified in one or more UNIQUE constraints. If foreign keys should only reference a PRIMARY key, and any other use of it is bad design, the initial objection then does not hold weight.
Yes, it can be done programmatically, and I see that you've done so with a package. As convenient as that is, the package is only a stop-gap measure, not to mention that the implementation is also incomplete. It only goes to illustrate that user-space code cannot replace actual functionality. RI is as critical an aspect of data management as strong typing and integrity constraints.
To have to maintain one aspect of RI elsewhere means that the feature is incomplete and inadequate, and it might as well be entirely maintained elsewhere. Even if assuming Oracle's feature set philosophy leans towards a particular type of database design, that Oracle lacks an auto-incrementing numeric column data type or the ability to tie a sequence to a column, a necessity for any design pattern that relies on using surrogate keys as opposed to updating natural keys, says that the system is design-pattern agnostic, if actually unfriendly towards the prescribed pattern.
In making your design assertion, it seems you are trying to fit a square peg that's slightly too big into a round hole that's slightly too small. Regardless, to justify the lack of a feature by some potential database design problem is dishonest. It does not address what functionality or lack thereof the feature provides, instead diverting the response to reference an abstraction that the remainder of the system does not adhere to anyway.
March 20, - pm UTC. If your primary key is not immutable, it is not a primary key. I don't understand why this is hard to understand?
Sort of circular. I have my principles, you have yours, they do not agree. My principle that a primary key is immutable is one of the founding thoughts behind the relational database - it is what it is. I've had posted for years before asktom even how to do this update cascade. And it is rather trivial to do the update cascade since 8i with deferrable constraints. I've talked of it, I've demonstrated it. If I put a check constraint on something - the database doesn't automagically fix your data - you programatically put in correct data or get an error and fix it.
It has always been that way. You never had the parent automagically created when the child was created did you? Same thing as you are asking for here. I hate despise, which it didn't exist, would love to get rid of on delete cascade. I fell even more so about update cascade. Sign up using Facebook. Sign up using Email and Password. Post as a guest Name. Email Required, but never shown. The Overflow Blog. Podcast Making Agile work for data science. Stack Gives Back Featured on Meta. New post summary designs on greatest hits now, everywhere else eventually.
Related Hot Network Questions. Question feed.
0コメント