Mixing
![My boss directly communicates with my subordinates without going through me [closed]](https://blogger.googleusercontent.com/img/b/R29vZ2xl/AVvXsEgjbpfN9tAutmK93bJRC3ZoROZzi2TJDms5n8_qJuhgE0a9b52OOHayv3NGT8igAdFL7byXNst-_1DZK5SjrIJ28_6RQPUpBROqMs5s6jo-ZsjX8kjDwfxJufIitH3TaQRXWaGSQKRQib-f/s72-c/1.jpg)
truncate table with foreign keys
Clash Royale CLAN TAG#URR8PPP
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty margin-bottom:0;
up vote
2
down vote
favorite
I have to truncate a table which gave me the below error.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
I fixed that by checking the below,
SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM dba_constraints p
JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
AND p.owner='SCHEMA_NAME'
/
alter table schema.table_name disable constraint SYS_constraint_name;
truncated the table.
Now I am trying to enable the constraint but getting the below,
enable constraint ora-02298 cannot validate - parent keys not found
So I use the below query to identify the records in child table,
SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)
I am unable to delete the above as they have child tables which are acting as foreign keys.
Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints
oracle foreign-key constraint
edited 4 hours ago
Akina
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
add a comment |Â
up vote
2
down vote
favorite
I have to truncate a table which gave me the below error.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
I fixed that by checking the below,
SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM dba_constraints p
JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
AND p.owner='SCHEMA_NAME'
/
alter table schema.table_name disable constraint SYS_constraint_name;
truncated the table.
Now I am trying to enable the constraint but getting the below,
enable constraint ora-02298 cannot validate - parent keys not found
So I use the below query to identify the records in child table,
SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)
I am unable to delete the above as they have child tables which are acting as foreign keys.
Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints
oracle foreign-key constraint
edited 4 hours ago
Akina
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago
add a comment |Â
up vote
2
down vote
favorite
up vote
2
down vote
favorite
I have to truncate a table which gave me the below error.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
I fixed that by checking the below,
SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM dba_constraints p
JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
AND p.owner='SCHEMA_NAME'
/
alter table schema.table_name disable constraint SYS_constraint_name;
truncated the table.
Now I am trying to enable the constraint but getting the below,
enable constraint ora-02298 cannot validate - parent keys not found
So I use the below query to identify the records in child table,
SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)
I am unable to delete the above as they have child tables which are acting as foreign keys.
Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints
oracle foreign-key constraint
edited 4 hours ago
Akina
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have to truncate a table which gave me the below error.
ORA-02266: unique/primary keys in table referenced by enabled foreign keys.
I fixed that by checking the below,
SELECT p.table_name "Parent Table", c.table_name "Child Table",
p.constraint_name "Parent Constraint", c.constraint_name "Child Constraint"
FROM dba_constraints p
JOIN dba_constraints c ON(p.constraint_name=c.r_constraint_name)
WHERE (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R' AND p.table_name = UPPER('TABLE_NAME')
AND p.owner='SCHEMA_NAME'
/
alter table schema.table_name disable constraint SYS_constraint_name;
truncated the table.
Now I am trying to enable the constraint but getting the below,
enable constraint ora-02298 cannot validate - parent keys not found
So I use the below query to identify the records in child table,
SELECT DISTINCT column_name FROM schema.table_name2 WHERE column_name NOT IN
(
SELECT column_name FROM schema.table_name1
)
I am unable to delete the above as they have child tables which are acting as foreign keys.
Now my question - instead of going through loops deleting and disabling keys. How can I truncate a given table and not disable constraints
oracle foreign-key constraint
oracle foreign-key constraint
edited 4 hours ago
Akina
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
Akina
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
edited 4 hours ago
Akina
2,287129
edited 4 hours ago
Akina
2,287129
edited 4 hours ago
Akina
2,287129
2,287129
asked 5 hours ago
ashanthan r
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 5 hours ago
ashanthan r
111
asked 5 hours ago
ashanthan r
111
111
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
ashanthan r is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago
add a comment |Â
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
up vote
2
down vote
Oracle 12c introduced TRUNCATE ... CASCADE:
TRUNCATE TABLE
CASCADE
If you specify CASCADE, then Oracle Database truncates all child
tables that reference table with an enabled ON DELETE CASCADE
referential constraint. This is a recursive operation that will
truncate all child tables, granchild tables, and so on, using the
specified options.
Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.
In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.
answered 34 mins ago
Balazs Papp
23.2k2930
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
add a comment |Â
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
up vote
2
down vote
Oracle 12c introduced TRUNCATE ... CASCADE:
TRUNCATE TABLE
CASCADE
If you specify CASCADE, then Oracle Database truncates all child
tables that reference table with an enabled ON DELETE CASCADE
referential constraint. This is a recursive operation that will
truncate all child tables, granchild tables, and so on, using the
specified options.
Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.
In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.
answered 34 mins ago
Balazs Papp
23.2k2930
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
add a comment |Â
up vote
2
down vote
Oracle 12c introduced TRUNCATE ... CASCADE:
TRUNCATE TABLE
CASCADE
If you specify CASCADE, then Oracle Database truncates all child
tables that reference table with an enabled ON DELETE CASCADE
referential constraint. This is a recursive operation that will
truncate all child tables, granchild tables, and so on, using the
specified options.
Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.
In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.
answered 34 mins ago
Balazs Papp
23.2k2930
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
add a comment |Â
up vote
2
down vote
up vote
2
down vote
Oracle 12c introduced TRUNCATE ... CASCADE:
TRUNCATE TABLE
CASCADE
If you specify CASCADE, then Oracle Database truncates all child
tables that reference table with an enabled ON DELETE CASCADE
referential constraint. This is a recursive operation that will
truncate all child tables, granchild tables, and so on, using the
specified options.
Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.
In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.
answered 34 mins ago
Balazs Papp
23.2k2930
Oracle 12c introduced TRUNCATE ... CASCADE:
TRUNCATE TABLE
CASCADE
If you specify CASCADE, then Oracle Database truncates all child
tables that reference table with an enabled ON DELETE CASCADE
referential constraint. This is a recursive operation that will
truncate all child tables, granchild tables, and so on, using the
specified options.
Keep in mind you need the constraints set to ON DELETE CASCADE for this to work.
In versions below 12c, yes, you can loop through, or instead of TRUNCATE, you can use DELETE, with ON DELETE CASCADE, which will take a lot longer.
answered 34 mins ago
Balazs Papp
23.2k2930
answered 34 mins ago
Balazs Papp
23.2k2930
answered 34 mins ago
Balazs Papp
23.2k2930
answered 34 mins ago
Balazs Papp
23.2k2930
23.2k2930
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
add a comment |Â
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
Would my idea work in version 11 or previous? (1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.)
– ypercubeᵀᴹ
13 mins ago
add a comment |Â
ashanthan r is a new contributor. Be nice, and check out our Code of Conduct.
ashanthan r is a new contributor. Be nice, and check out our Code of Conduct.
ashanthan r is a new contributor. Be nice, and check out our Code of Conduct.
ashanthan r is a new contributor. Be nice, and check out our Code of Conduct.
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
StackExchange.ready(
function ()
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f218688%2ftruncate-table-with-foreign-keys%23new-answer', 'question_page');
);
Post as a guest
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
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
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
I don't think you can. I think the only possible way is: 1.Disable FK constraints. 2. TRUNCATE tables (starting from children, then parents). 3. Enable FK constraints.
– ypercubeᵀᴹ
39 mins ago