1

I have an employee table with the following columns:

fname (varchar), lname (varchar), id (numeric)

id is the primary key.

There is a table name called works_on with columns 

projectname (varchar), id (numeric)

Here, id is a foreign key that references the employee table.

When I was trying delete a row from the employee table like this:

delete from employee where id = 1

I get this error:

update or delete on table "employee" violates foreign key constraint "works_on_id_fkey" on table "works_on"`.

I am new to the database management system.

Any solution?

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
akhil
  • 1,649
  • 3
  • 19
  • 31

2 Answers2

4

As employee is a foreign key in table works_on; the reason you are unable to delete employee ID 1 is because employee ID 1 exists on works_on (or perhaps other tables in which employee is a foreign key). The system is trying to maintain integrity of the database by preventing you from deleting an employee affiliated with works_on.

Say the system let you delete the employee record. Now when you look at the works_on table what would employee 1 relate to? You can no longer look up first/last names among other information. So the system is saying: If you want to delete the employee record, you must first remove/alter the foreign key associations to other system records; to ensure their continued integrity. If the system let you do this it would be called "Orphaning" a record. the parent record to which the child associates no longer exists.

To resolve a few options are:

  • Create a procedure that deletes employees but first checks any tables in which employee is a foreign key and ensures it's ok to delete those as well; and then deletes those records before deleting the employee record. (this can cause a massive daisy chain if those tables have PK's to which other tables are FK. But that's the nature of RDBMS.
  • Create a feature that lets you assign such records to employee 1's replacement or removes such records if no longer relevant.
  • enable ON DELETE CASCADE ON UPDATE CASCADE, which will automatically delete child records if parent record is deleted. (BE VERY CAREFUL AND CONSIDER how this impacts your system before enabling) Example: Docs
  • Don't delete the record, instead maintain a status field showing active/inactive and use it as a control mechanism to show or not show employees and their associated records.
  • There's several other options to consider as well; you must ask yourself, or the business for which this is being developed, what should happen to all those records in which employee 1 is a foreign key. Delete some/All, reassign some delete some? Prompt the user for how they want to handle each instance? Simply Inform the user they must first address the constraints found in (List all places this employee has a FK relationship?) and ensure they have a way to handle all those places... Lots of options.
xQbert
  • 34,733
  • 2
  • 41
  • 62
  • is it possible to alter the employee table by adding on update cascade on delete cascade? – akhil Sep 30 '18 at 15:35
  • Based on [this](https://stackoverflow.com/questions/10356484/how-to-add-on-delete-cascade-constraints) answer: No. but that applies to version 8 and likely before and I don't know which version you are on. Though this Q&A on PostgreSQL site seems to indicate it would work: https://www.postgresql.org/message-id/20060307224214.GA21890%40crowfix.com I don't have a postgresql environment to know which will work; but I trust their online docs. – xQbert Sep 30 '18 at 15:39
-5

You can not delete a row by that way. Because it has the constraint id in it (works_on_id_fkey). If you want to delete, you have to remove constraint from it.

alter table employee drop foreign key works_on_id_fkey
jame focus
  • 1
  • 5
  • 3
    Disabling database integrity... does that seem like a wise move? How about checking to see if the records associated should also be deleted and remove those. or enable a cascade delete on the table DDL. Someone setup a database with this relationship for a reason. Why would we want to bypass it? – xQbert Sep 30 '18 at 14:38
  • Not a wise move? of course, it is a not a smart move, we can not do this when we worked on a big database with hundreds of tables. But he is new to DBMS, he will figure it out when he fully understand what is primary key, foreign key and constraint,... – jame focus Sep 30 '18 at 14:47
  • I apologize. My intended point was to have you consider a more complete response. If this question went on to explain why this works but the consequences of doing so; or offered alternative options while maintaining integrity I would happily withdraw my comment. As it stands, if the user implemented it; it would cause harm to the integrity of the database and does nothing to properly educate the question being asked. (both schools of hard knocks failed here :P) – xQbert Sep 30 '18 at 15:00
  • Yes, I understand what are you trying to say and I am aware of doing this will cause a severe problem to the whole database. But questioner must know what is foreign key and what it impacts to other elements in a table. And by asking this question, he must be a new guy to DBMS. I am sure that he will understand after he deletes that row, – jame focus Sep 30 '18 at 15:09