-1

This is more a theoretical question. Let's say you have the following table:

(ID, FirstName, LastName, Street, ZIP, Country) ID is primary key

Then it is possible that you have two or more people with the same name living at the same address. So when you query for them by firstname, lastname, streetm zip and country, you get several results. Which makes sense, because it may happen that in a large building, two people of the same name live.

How would you delete a person from that table, if its ID is used elsewhere as a foreign key? Wouldn't it be impossible? You have no way of telling, which of the two "Steven Smith ABC Blvd. 2 1111 USA" is the one you want...?

EDIT: A little clearer:

Let's say, in the table above, I have two customers with the same name and address, so only dinstinct in their primary key. Their ID is used in other tables as foreign keys, so I cant just delete one customer and say "right, now I still have a Steve Smith living at ..." because the other Steve Smith is referenced in other tables.

How would you delete the first Steve Smith correctly?

Riad
  • 3,822
  • 5
  • 28
  • 39
user3629892
  • 2,960
  • 9
  • 33
  • 64
  • If this person has a foreign key to another table then you know him from the unique id – juergen d Jan 09 '15 at 07:38
  • which DBMS are you using? Postgres? Oracle? –  Jan 09 '15 at 07:40
  • @a_horse_with_no_name: Let's say it's MySQL :) – user3629892 Jan 09 '15 at 07:52
  • @juergend: The ID here is used as a foreign key in another table. – user3629892 Jan 09 '15 at 07:55
  • OK then, what is your question exactly? – juergen d Jan 09 '15 at 07:58
  • So you want to delete the one not being referenced elsewhere? – juergen d Jan 09 '15 at 08:08
  • Best practice dictates that whenever a surrogate PK is used (like your id column) a unique natural key should also be provided. This might be a composite key formed on something like name and date of birth. – Strawberry Jan 09 '15 at 08:14
  • From what you are describing, you can't tell them apart based on your Customer table. To help it from that end (in the future), you could distribute the ID to (all of) your customers, and ask them to include it in any request. Otherwise (or when they do not have the Customer ID at hand), you would need to search the tables using the Customer ID as a foreign key, to find some information (such as an order for some particular product), which only one of them did buy. This will show you, which ID belongs to which person. Once you know the correct ID, you know, which records to keep/delete. – Abecee Jan 09 '15 at 09:42
  • thanks, thats what I wanted to know! Thanks a lot! – user3629892 Jan 12 '15 at 11:47

1 Answers1

0

If you want to delete a Steve Smith who is not being referenced in another table then do you left join on that table

delete a
from addressTable a
left join secondTable s on s.fkAddressId = a.id
where s.fkAddressId is null
and a.firstName = 'Steve' and a.lastName = 'Smith'
juergen d
  • 201,996
  • 37
  • 293
  • 362