Good day,

I want to delete a record from a single table but some of the filed on sigle record is related to other table on my database.

how would i delete it..

i used this script

delete from faculty deptid = (select deptCode from dept)

all i want is to remove the single faculty record on the faculty table only...

6 Years
Discussion Span
Last Post by cyberdaemon

If your database doesn't have cascade on delete,
you must first delete the records from the other tables related to the records you want to delete.

This is due the the relationships of table, if you have a primary key on the main table that you wanted to delete an specific record, but it is being used as a foreign key to other tables, then you must delete first the records in the other tables before you are able to delete the record on the main table.

Hope it helps.


Consider a table Tab1 which has a primary key which is referenced(foreign key) by a primary key of table tab2.
Now if you want to delete Tab2, then just write:

drop table Tab2;

But if you want to delete table Tab2 then since its Primary Key has a referential constraint..you have to write:

drop table Tab2 cascade constraints;

This will delete table Tab2 and also remove the foreign key reference of Tab1.

After this delete primary key of Tab2 has no relation with primary key of Tab1.

Now, if you wanna delete a particular row from a table then just write:

delete from <tablename> where <primarykey>=<primary key value of the row you wanna delete> ;

The above query will succesfully delete that particular row.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.