| Cascade Delete? How do I implement this?? [message #372163] |
Wed, 17 January 2001 09:53  |
Ryan Dorosh
Messages: 12
Registered: October 2000
|
Junior Member |
|
|
I have a table in which I want to delete a record that has FK's to many dependent tables.
Without manually deleting all the child records, how can I delete the primary record and have all the FK's deleted as well? I've heard about the cascade option but I don't know how to implement this.
Thanks in advance,
Ryan.
|
|
|
|
|
|
|
|
| Delete & truncate [message #372483 is a reply to message #372163] |
Wed, 14 February 2001 22:57  |
Anantha krishna
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
dear sir/madam
Pl send me mail which contain differences between delete and truncate including syntax..
I am mailing from my freind's login
with regards
ananthakirhna
|
|
|
|
| Delete & truncate [message #372484 is a reply to message #372163] |
Wed, 14 February 2001 23:52 |
Anantha krishna
Messages: 3
Registered: February 2001
|
Junior Member |
|
|
dear sir/madam
Pl send me mail which contain differences between delete and truncate including syntax..
I am mailing from my freind's login
with regards
ananthakirhna
|
|
|
|
| Re: Delete & truncate [message #372486 is a reply to message #372484] |
Thu, 15 February 2001 03:30 |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hi,
The difference between the Delete and Truncate command is follows:
truncate is ddl command;
but delete is dml command.
Autocommit will take place after ddl command i.e., the data will be stored in the memory after the ddl command automatically.
Truncate command:
As truncate is ddl command,when you give the command:
consider this table employee which has two rows:
SQL> select * from employee;
SNO NAME EMPID DEPT DESG
------- --------------- --------- ---------------
1 gayathri 324 marketing Counsellor
2 selva 234 technical faculty
When we truncate this table, the data in the truncated table is deleted and the memory space is also retrieved.
SQL> truncate table employee;
Table truncated.
Even u truncate the table, the structure of the table will be available.
If u give the desc command, u will get the structure of the table.
SQL> desc employee;
Name Null? Type
--------------------------------------------------ENO NOT NULL NUMBER
NAME VARCHAR2(15)
EMPID NOT NULL NUMBER(3)
DEPT CHAR(15)
DESG NOT NULL VARCHAR2(15)
If you want to get the values back then it is not possible as this command(truncate) will delete the data in the table permanently.
Delete command:
consider the table stud1 with a single row(any no. of rows can be there):
SQL> select * from stud1;
NAME ROLL MAR MAR MAR
----------------------- --------------- --- --- --
Anand r345 56 67 78
SQL> delete from stud1;
row deleted.
The data in the table stud1 is deleted. Now if we select that row the output follows:
SQL> select * from stud1;
no rows selected
If u check the structure of the table, it is available.
SQL> desc stud1;
Name Null? Type
---------- ------------------------------
NAME VARCHAR2(25)
ROLL VARCHAR2(15)
MARK1 VARCHAR2(3)
MARK2 VARCHAR2(3)
MARK3 VARCHAR2(3)
If you give roll back command, then it will undo this deleting action.
SQL> roll back stud1;
Rollback complete.
As the delete command is dml command, and there is no autocommit, we could able to get back the data deleted immediately doing the roll back.
If u select the table stud1 the output follows:
U could able to get the data deleted.
SQL> select * from stud1;
NAME ROLL MAR MAR MAR
----------------------- --------------- --- anand r345 56 67 78
could u get it now?
Gayathri.
|
|
|
|
| Re: Delete & truncate [message #372487 is a reply to message #372484] |
Thu, 15 February 2001 03:34 |
Gayathri
Messages: 19
Registered: February 2001
|
Junior Member |
|
|
Hi,
The difference between the Delete and Truncate command is follows:
truncate is ddl command;
but delete is dml command.
Autocommit will take place after ddl command i.e., the data will be stored in the memory after the ddl command automatically.
Truncate command:
As truncate is ddl command,when you give the command:
consider this table employee which has two rows:
SQL> select * from employee;
SNO NAME EMPID DEPT DESG
------- --------------- --------- ---------------
1 gayathri 324 marketing Counsellor
2 selva 234 technical faculty
When we truncate this table, the data in the truncated table is deleted and the memory space is also retrieved.
SQL> truncate table employee;
Table truncated.
Even u truncate the table, the structure of the table will be available.
If u give the desc command, u will get the structure of the table.
SQL> desc employee;
Name Null? Type
----------------------------
ENO NOT NULL NUMBER
NAME VARCHAR2(15)
EMPID NOT NULL NUMBER(3)
DEPT CHAR(15)
DESG NOT NULL VARCHAR2(15)
If you want to get the values back then it is not possible as this command(truncate) will delete the data in the table permanently.
Delete command:
consider the table stud1 with a single row(any no. of rows can be there):
SQL> select * from stud1;
NAME ROLL MAR MAR MAR
----------------------- --------------- --- --- --
Anand r345 56 67 78
SQL> delete from stud1;
row deleted.
The data in the table stud1 is deleted. Now if we select that row the output follows:
SQL> select * from stud1;
no rows selected
If u check the structure of the table, it is available.
SQL> desc stud1;
Name Null? Type
---------- ------------------------------
NAME VARCHAR2(25)
ROLL VARCHAR2(15)
MARK1 VARCHAR2(3)
MARK2 VARCHAR2(3)
MARK3 VARCHAR2(3)
If you give roll back command, then it will undo this deleting action.
SQL> roll back stud1;
Rollback complete.
As the delete command is dml command, and there is no autocommit, we could able to get back the data deleted immediately doing the roll back.
If u select the table stud1 the output follows:
U could able to get the data deleted.
SQL> select * from stud1;
NAME ROLL MAR MAR MAR
----------- --------------- ---
anand r345 56 67 78
could u get it now?
Gayathri.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
