Talk:Foreign key

procedural versus declarative

is it worth mentioning the tension between declarative and procedural notations here? FK constraints as declarataive and equivalent triggers as procedural is a perfect example —Preceding unsigned comment added by 75.87.134.32 (talk) 18:53, 22 April 2009 (UTC)

Remark

what is key and y we are using primary key and foriegn key in normalization of database.

how do you actually perform a Forgein Key!? i know how primary key looks like, what does a Foreign Key look like? >x<ino 10:58, 2 December 2005 (UTC)

Oy! It took me forever to get to anything that actually explained it. So I added a sentence in the middle to explain it. MikeyTheK 22:22, 10 February 2007 (UTC)

I rewrote most of the article because (so far) the article only gave some examples instead of explaining the concepts of FKs. --Stolze 12:48, 14 February 2007 (UTC)

i want definitions sort and clear,not lengthy as to suit students —Preceding unsigned comment added by 117.97.4.228 (talk) 02:47, 11 September 2007 (UTC)

FK

The repeated use of the unnecessary abbreviation FK was non-encyclopaedic. Removed.—Preceding unsigned comment added by 86.138.228.61 (talk • contribs)

As far as i can tell , in the section titled "Defining Foreign Keys", the syntax offered for column level referencial constraint in CREATE TABLE:

col3 INTEGER FOREIGN KEY REFERENCES other_table(column_name)

is not correct, according to sql:2003. Instead, it should be:

col3 INTEGER REFERENCES other_table(column_name)

In other words, the FOREIGN KEY keywords should not be there. In fact, the postgresql version shown in the next example is correct according to syntax.

Gnarinn (talk) 17:03, 27 March 2008 (UTC)

Vendor support

Worth mentioning that some major DBMS vendors (MySQL[1]) recognize the statement syntax but don't actually support foreign keys, which means that this functionality will fail silently. Very bad. Ham Pastrami (talk) 06:12, 22 April 2008 (UTC) Clarify: In MySQL you can get foreign keys with the InnoDB engine, but not with other table types. Ham Pastrami (talk) 06:23, 22 April 2008 (UTC)

What does ON UPDATE CASCADE do?

ON UPDATE CASCADE is in the example. Which means, when the referenced field is modified, the referencing field gets modified too, to the same value.

Turning Foreign Keys Into Triggers

Foreign key constraints are a shorthand for specifying certain triggers, which are semantically equivalent to the constraints. Here is the genfkey utility from SQLite, as a web service. 75.87.134.77 (talk) 02:42, 20 April 2009 (UTC)

Technical level

There currently is a link in this article: For a simpler visualization, see VisualCase.com[2] This visualization might serve as a much better example for this article other than DML abstractly describing foreign keys. Group29 (talk) 21:55, 9 June 2009 (UTC) Added Wikipedia:Make technical articles accessible template to this discussion. Group29 (talk) 21:58, 9 June 2009 (UTC)

First few sentences stink...

This took about four re-reads:

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a set of columns in another (referenced) table. The columns in the referencing table must be the primary key or other candidate key in the referenced table. The values in one row of the referencing columns must occur in a single row in the referenced table. Thus, a row in the referencing table cannot contain values that don't exist in the referenced table (except potentially NULL).

I suggest re-writing to simplify and remove the need for parenthesis. —Preceding unsigned comment added by 76.111.60.215 (talk) 03:12, 23 May 2011 (UTC)

I re-wrote the introduction trying to fix this problem, also some slight mis-statements. Ngriffeth (talk) 03:34, 26 May 2013 (UTC)

Fundamental Misunderstanding

Much of the article, and especially the very first sentence, is wrong - it conflates the concept of a foreign key with the use of PK-FK constraints to enforce RI. This is a pretty common misunderstanding, but it really shouldn't be perpetuated here! Grumpypierre (talk) 22:39, 30 May 2011 (UTC)

That was my own feeling too, but I have been away from intense involvement in databases for a few years and when I looked in a number of different standard textbooks for discussions of foreign keys, it appears that the idea of foreign key and the idea of referential integrity are inter-twined. I tried to reflect this accurately in my re-write. Ngriffeth (talk) 03:37, 26 May 2013 (UTC)

foreign key example

there should be some foreign keys examples--Mdtr (talk) 02:32, 31 August 2011 (UTC)

Poorly explained

This is definitely one of the most vague, ambiguous, and poorly described things I have seen in wikipedia. It tries to cram too much explanation into incomprehensible sentences. In particular, the example for the "order" and "customer" tables needs an explanation from both directions. Also, delete cascades are almost completely unexplained. What happens if there are multiple orders that reference a customer? Does it only delete the customer after the last order referencing the customer is deleted? Does deleting the customer delete all the orders that reference it? Is there a way to make it one-way? Is there a way to have a customer that has no orders? Is there a way to enforce that any customer that exists must have an order? Which table would those constraints go in? Thanks. — Preceding unsigned comment added by 99.253.53.196 (talk) 01:54, 12 September 2013 (UTC)

26 jan 2016 - first line 'simpler explanation' is grammatically incorrect — Preceding unsigned comment added by 205.215.117.41 (talk) 19:04, 26 January 2016‎

Candidate key or Superkey?

The article talks about the foreign key referencing a Candidate key in the referenced table. I dont think that this is entirely accurate. The foreign key must reference a Superkey, but there is no requirement for that Superkey to also be a Candidate key. Candidate key is a smallest possible superkey, and I'm pretty sure a foreign key can reference a superkey which is not in its minimal-most form (ie. is not a candidate key). Jesselong (talk) 13:36, 18 December 2013 (UTC)

Defining foreign keys

What does that "Foreign keys can be defined with a stored procedure statement" mean? I don't understand it, and I can't find anything like that in the ANSI/ISO SQL specification. Fjerdingen (talk) 21:09, 10 August 2017 (UTC)

Triggers

I can't understand the code example. Where does sum come from?

Besides, the syntax isn't ANSI SQL compliant. Why proprietary code in a general SQL article? Fjerdingen (talk) 21:11, 10 August 2017 (UTC)

Article is lousy

This article seems like a great example of one where it's best to simply start over. The lead is too technical. The structure is weird. The bulk of the text is semi-off topic. There's no simple examples. The rest of the examples are too niche. Basically awful. Anybody oppose, basically starting as a simple stub and growing it back? Jason Quinn (talk) 12:17, 6 November 2020 (UTC)