1

If I create a foreign key is referential integrity automatic? Do I have to set anything else to make cascading work?

edit: for example, in postgres. I mean by automatic that I don't need to set anything else up to make RI work. Deletes. I am guessing though a FK means that it will insure data is present in the FK for the insert.

johnny
  • 19,272
  • 52
  • 157
  • 259
  • 1
    "automatic"? What do you think you mean by "automatic"? "make cascading work?" What do you mean by this? Are you asking about "cascade delete"? Please be much more specific so we know what you're talking about. – S.Lott Sep 12 '11 at 21:14

3 Answers3

7

Yes, Referential Integrity is enforced as soon as you create a foreign key. You can define other options as well, such as cascading updates and deletes. Usually this is part of the same syntax used to create the foreign key and it normally has to be specified when the foreign key is created - at least that's how it works in standard SQL.

nvogel
  • 24,981
  • 1
  • 44
  • 82
  • Does "create a foreign key" mean the same thing as "declaring a foreign key constraint"? If so, then your anser is correct. But if not, then it's psossible to create something that is used like a foreign key in queries, and if functionally equivalent to a foreign key, but is not know to be a foreign key by the DBMS. In this scenario, it's conceivable that the applications enforce refernetial integrity whenever the database is modified. But that's a strategy I wouldn't recommend. – Walter Mitty Sep 13 '11 at 19:42
  • I have no idea what that comment means. – Stephanie Page Sep 21 '11 at 14:24
  • @Stephanie, I think Walter's valid point is that a foreign key is *not* a constraint - it is the set of columns to which an RI constraint applies. I and others have interpreted "foreign key" in this question to mean *the FOREIGN KEY constraint syntax used in SQL*. That may or may not be what was meant by the question. – nvogel Sep 22 '11 at 12:35
2

There is more to referential integrity than foreign keys.

When it comes to cascades, it depends on how you define the foreign keys and on the RDBMS you are using.

Oded
  • 489,969
  • 99
  • 883
  • 1,009
  • Wouldn't Referential Integrity be available as soon as you have a FK regarless of cascades? Is there anything I'm missing? – Raj More Sep 12 '11 at 21:54
  • Can you elaborate on the rest of "Referential Integrity"? Your comment sounds very mysterious. – Stephanie Page Sep 20 '11 at 21:10
  • @StephaniePage - Only that in some designs, in order to maintain referential integrity, foreign keys are not enough. Say you model an Person table, and two linked tables: Manager and Subordinate. Manager and Subordinate have a personId but can't have the same ones. Foreign keys alone will not keep referential integrity. – Oded Sep 21 '11 at 09:48
  • 1
    Ah good point. There are lots of relationships which declarative constraints cannot address. This is one of the major deficiencies in RDBMS. Newer richer declarative constraints would be a huge improvement. – Stephanie Page Sep 21 '11 at 15:00
  • @Stephanie, lacking support for constraints of the type Oded described is not a "major deficiency" in RBDMS. It's a major deficiency of SQL and its implementations. There's no reason why a RBDMS can't and shouldn't support such constraints. – nvogel Sep 28 '11 at 13:26
  • I know of no RBDMS which faithfully supports ANSI SQL Standard and ONLY ANSI SQL Standard. They have extensions which make certain tasks easier. Don't you agree that any vendor could add those constraints despite the lack of a ANSI standard for them? – Stephanie Page Sep 29 '11 at 19:26
  • Yes of course I agree. What I disagreed with was your apparent suggestion that it was a deficiency of RDBMS in general - meaning that any DBMS that follows the relational model would have such limitations. BTW, such constraints *are* part of ISO Standard SQL, specifically the CREATE ASSERTION syntax. – nvogel Oct 23 '11 at 15:46
1

If I create a foreign key is referential integrity automatic?

No.

Do I have to set anything else to make cascading work?

The details depend on the database system that you are using, but something like "ON DELETE CASCADE" is probably what you want.

Bob Stine
  • 905
  • 10
  • 13
  • I upvoted your answer mainly to cancel out somebody else's downvote. Technically, you are correct. it's possible to create a foreign key, and use it like a foreign key, without telling the DBMS that it is a foreign key, by declaring a constraint. However, your answer is too short, because the questioner may have wanted to know what heppens when you do declare a foriegn key constraint. If that's what was being asked, then your answer is misleading. – Walter Mitty Sep 13 '11 at 19:46