How to apply both on delete and on update cascade simultaneously in oracle12c?
I'm beginer and I'm working on oracle 12c database so, In my database project I want to apply cascade on delete and on update simultaneously as i did in mysql but when i apply tha same technique in oracle it show me the error so how can i do that?
oracle
asked Dec 27 '18 at 16:14
MImranKhan
176
add a comment |
I'm beginer and I'm working on oracle 12c database so, In my database project I want to apply cascade on delete and on update simultaneously as i did in mysql but when i apply tha same technique in oracle it show me the error so how can i do that?
oracle
asked Dec 27 '18 at 16:14
MImranKhan
176
add a comment |
I'm beginer and I'm working on oracle 12c database so, In my database project I want to apply cascade on delete and on update simultaneously as i did in mysql but when i apply tha same technique in oracle it show me the error so how can i do that?
oracle
asked Dec 27 '18 at 16:14
MImranKhan
176
I'm beginer and I'm working on oracle 12c database so, In my database project I want to apply cascade on delete and on update simultaneously as i did in mysql but when i apply tha same technique in oracle it show me the error so how can i do that?
oracle
oracle
asked Dec 27 '18 at 16:14
MImranKhan
176
asked Dec 27 '18 at 16:14
MImranKhan
176
asked Dec 27 '18 at 16:14
MImranKhan
176
asked Dec 27 '18 at 16:14
MImranKhan
176
asked Dec 27 '18 at 16:14
MImranKhan
176
176
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.
More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.
answered Dec 27 '18 at 16:17
lau
2413
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to haveON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).
– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947873%2fhow-to-apply-both-on-delete-and-on-update-cascade-simultaneously-in-oracle12c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.
More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.
answered Dec 27 '18 at 16:17
lau
2413
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to haveON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).
– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
add a comment |
There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.
More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.
answered Dec 27 '18 at 16:17
lau
2413
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to haveON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).
– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
add a comment |
There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.
More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.
answered Dec 27 '18 at 16:17
lau
2413
There is no ON UPDATE CASCADE on Oracle. While you can probably argue updating a table's primary key is valid in SQL, you probably should not, hence the decision of Oracle not to implement it.
More info here:
https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5773459616034
EDIT: As discussed in comments below, think of that constraint as a way Oracle prevents people from doing something wrong (updating primary keys).
The correct way to handle the case of a primary key that might be updated is to create a separate field that will act as the surrogate primary key. The surrogate key, of course, is immutable.
The danger of using a natural key as primary key is discussed there.
answered Dec 27 '18 at 16:17
lau
2413
edited Dec 28 '18 at 7:14
answered Dec 27 '18 at 16:17
lau
2413
answered Dec 27 '18 at 16:17
lau
2413
answered Dec 27 '18 at 16:17
lau
2413
2413
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to haveON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).
– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
add a comment |
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to haveON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).
– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Updating PK - valid or not it is possible with deferred constraints. I did similar thing on Postgresql. Oracle Deferred Constraints
– Lukasz Szozda
Dec 27 '18 at 16:34
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to have
ON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).– lau
Dec 27 '18 at 16:47
Deferred constraints is a possibility mentioned in my link indeed. BTW, I am also a postgresql user an although it allows to have
ON UPDATE CASCADE, I would never do it (I agree with Tom's point that a primary key should never be updated).– lau
Dec 27 '18 at 16:47
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
"Should" or "could" are two different things. It's better to have an option and never use it :)
– Lukasz Szozda
Dec 27 '18 at 16:48
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
As long as it is under control I would say ... I like the idea of not having the option at all if it prevents me/coworkers from doing mistakes. If from the start you work with the idea a primary key is to never be updated, you will avoid preparing your downfall in the future. Check out the answer of stackoverflow.com/questions/337503/… (especially starting point 3 + down to the comments).
– lau
Dec 27 '18 at 16:56
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
Fair enough. :)
– Lukasz Szozda
Dec 27 '18 at 17:00
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f53947873%2fhow-to-apply-both-on-delete-and-on-update-cascade-simultaneously-in-oracle12c%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
