How i can solve ORACLE problem in foreign key
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a problem in oracle and I need help. I have the following query:
1 CREATE TABLE TEST1 (
2 NAME VARCHAR(20)
3 ID VAR(9)
4 PRIMARY KEY(ID)
5 FOREIGN KEY(NAME) References TEST2(ANAME)
6 ON DELETE CASCADE ON UPDATE SET NULL );
If I want to delete line #6 what should i do?
oracle
edited Nov 23 '18 at 14:48
AlpacaFur
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
add a comment |
I have a problem in oracle and I need help. I have the following query:
1 CREATE TABLE TEST1 (
2 NAME VARCHAR(20)
3 ID VAR(9)
4 PRIMARY KEY(ID)
5 FOREIGN KEY(NAME) References TEST2(ANAME)
6 ON DELETE CASCADE ON UPDATE SET NULL );
If I want to delete line #6 what should i do?
oracle
edited Nov 23 '18 at 14:48
AlpacaFur
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33
add a comment |
I have a problem in oracle and I need help. I have the following query:
1 CREATE TABLE TEST1 (
2 NAME VARCHAR(20)
3 ID VAR(9)
4 PRIMARY KEY(ID)
5 FOREIGN KEY(NAME) References TEST2(ANAME)
6 ON DELETE CASCADE ON UPDATE SET NULL );
If I want to delete line #6 what should i do?
oracle
edited Nov 23 '18 at 14:48
AlpacaFur
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
I have a problem in oracle and I need help. I have the following query:
1 CREATE TABLE TEST1 (
2 NAME VARCHAR(20)
3 ID VAR(9)
4 PRIMARY KEY(ID)
5 FOREIGN KEY(NAME) References TEST2(ANAME)
6 ON DELETE CASCADE ON UPDATE SET NULL );
If I want to delete line #6 what should i do?
oracle
oracle
edited Nov 23 '18 at 14:48
AlpacaFur
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
edited Nov 23 '18 at 14:48
AlpacaFur
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
edited Nov 23 '18 at 14:48
AlpacaFur
163211
edited Nov 23 '18 at 14:48
AlpacaFur
163211
edited Nov 23 '18 at 14:48
AlpacaFur
163211
163211
asked Nov 23 '18 at 14:31
HailahHailah
61
asked Nov 23 '18 at 14:31
HailahHailah
61
asked Nov 23 '18 at 14:31
HailahHailah
61
61
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33
add a comment |
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33
add a comment |
1 Answer
1
active
oldest
votes
"How I can change the value of primary key and based of that the foreign keys of this pk will change too?"
First, you should never need to do that. Primary keys like this are really just numbers that identify a row, they have no meaning in themselves. It's like asking how you would change the ROWID of a row.
If you must, you could:
- Find the foreign keys pointing to this table and disable them with
ALTER CONSTRAINT myconstraint DISABLE
- Update your primary table and catch the new id value with
UPDATE test1 SET id = mysequence.NEXTVAL WHERE id = :oldid RETURNING id INTO :newid, assuming it's set by a sequence. - Update the ids in your other tables with the new id.
- Reenable your constraints.
Note that altering constraints is DDL and will do an implicit commit and this approach will leave your tables unprotected by the foreign key constraints.
A second approach would be to:
- Insert a new row in the primary table and catch the new id.
- Update the id in the foreign tables with the new id.
- Delete the old row in the primary table.
Now that I think about it, that second approach seems better to me. No DDL and it just seems cleaner.
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
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%2f53448557%2fhow-i-can-solve-oracle-problem-in-foreign-key%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
"How I can change the value of primary key and based of that the foreign keys of this pk will change too?"
First, you should never need to do that. Primary keys like this are really just numbers that identify a row, they have no meaning in themselves. It's like asking how you would change the ROWID of a row.
If you must, you could:
- Find the foreign keys pointing to this table and disable them with
ALTER CONSTRAINT myconstraint DISABLE
- Update your primary table and catch the new id value with
UPDATE test1 SET id = mysequence.NEXTVAL WHERE id = :oldid RETURNING id INTO :newid, assuming it's set by a sequence. - Update the ids in your other tables with the new id.
- Reenable your constraints.
Note that altering constraints is DDL and will do an implicit commit and this approach will leave your tables unprotected by the foreign key constraints.
A second approach would be to:
- Insert a new row in the primary table and catch the new id.
- Update the id in the foreign tables with the new id.
- Delete the old row in the primary table.
Now that I think about it, that second approach seems better to me. No DDL and it just seems cleaner.
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
add a comment |
"How I can change the value of primary key and based of that the foreign keys of this pk will change too?"
First, you should never need to do that. Primary keys like this are really just numbers that identify a row, they have no meaning in themselves. It's like asking how you would change the ROWID of a row.
If you must, you could:
- Find the foreign keys pointing to this table and disable them with
ALTER CONSTRAINT myconstraint DISABLE
- Update your primary table and catch the new id value with
UPDATE test1 SET id = mysequence.NEXTVAL WHERE id = :oldid RETURNING id INTO :newid, assuming it's set by a sequence. - Update the ids in your other tables with the new id.
- Reenable your constraints.
Note that altering constraints is DDL and will do an implicit commit and this approach will leave your tables unprotected by the foreign key constraints.
A second approach would be to:
- Insert a new row in the primary table and catch the new id.
- Update the id in the foreign tables with the new id.
- Delete the old row in the primary table.
Now that I think about it, that second approach seems better to me. No DDL and it just seems cleaner.
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
add a comment |
"How I can change the value of primary key and based of that the foreign keys of this pk will change too?"
First, you should never need to do that. Primary keys like this are really just numbers that identify a row, they have no meaning in themselves. It's like asking how you would change the ROWID of a row.
If you must, you could:
- Find the foreign keys pointing to this table and disable them with
ALTER CONSTRAINT myconstraint DISABLE
- Update your primary table and catch the new id value with
UPDATE test1 SET id = mysequence.NEXTVAL WHERE id = :oldid RETURNING id INTO :newid, assuming it's set by a sequence. - Update the ids in your other tables with the new id.
- Reenable your constraints.
Note that altering constraints is DDL and will do an implicit commit and this approach will leave your tables unprotected by the foreign key constraints.
A second approach would be to:
- Insert a new row in the primary table and catch the new id.
- Update the id in the foreign tables with the new id.
- Delete the old row in the primary table.
Now that I think about it, that second approach seems better to me. No DDL and it just seems cleaner.
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
"How I can change the value of primary key and based of that the foreign keys of this pk will change too?"
First, you should never need to do that. Primary keys like this are really just numbers that identify a row, they have no meaning in themselves. It's like asking how you would change the ROWID of a row.
If you must, you could:
- Find the foreign keys pointing to this table and disable them with
ALTER CONSTRAINT myconstraint DISABLE
- Update your primary table and catch the new id value with
UPDATE test1 SET id = mysequence.NEXTVAL WHERE id = :oldid RETURNING id INTO :newid, assuming it's set by a sequence. - Update the ids in your other tables with the new id.
- Reenable your constraints.
Note that altering constraints is DDL and will do an implicit commit and this approach will leave your tables unprotected by the foreign key constraints.
A second approach would be to:
- Insert a new row in the primary table and catch the new id.
- Update the id in the foreign tables with the new id.
- Delete the old row in the primary table.
Now that I think about it, that second approach seems better to me. No DDL and it just seems cleaner.
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
answered Nov 24 '18 at 23:06
eaolsoneaolson
8,79663247
8,79663247
add a comment |
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.
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%2f53448557%2fhow-i-can-solve-oracle-problem-in-foreign-key%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
ON UPDATE CASCADE is not valid in Oracle, So, just edit your statement to remove that clause.
– APC
Nov 23 '18 at 14:44
If you want to delete line #6, then just delete it. What exactly is your question?
– a_horse_with_no_name
Nov 23 '18 at 14:48
How I can change the value of primary key and based of that the foreign keys of this pk will change too ??
– Hailah
Nov 23 '18 at 15:33