Talk:Primary key

Dewey system as primary key?

I disagree with the dewey system as an example of a primay key, multiple books can have the same classification numbers.

Surely, a better listing here would be ISBN? --Grand Edgemaster 22:00, 12 September 2005 (UTC)

- Never mind, --Grand Edgemaster 22:01, 12 September 2005 (UTC)

Where the heck did "immutable" come from?

The primary key should be immutable, meaning that its value should not be changed during the course of normal operations of the database. (Recall that a primary key is the means of uniquely identifying a tuple, and that identity, by definition, never changes.) This avoids the problem of dangling references or orphan records created by other relations referring to a tuple whose primary key has changed. If the primary key is immutable, this can never happen.

Huh? Is there some kind of reference to back this up? If not, I'm going to delete it. Neither the SQL Standard nor the writings of EF Codd or CJ Date say anything about the immutability of keys. I think the author of that paragraph is mis-applying Object-Oriented theory to relational databases. --Josh Berkus, PostgreSQL Project 1/23/06

FWIW, I agree with deleting it, but I thought that in many real SQL DMBSs updating primary keys is not allowed. So perhaps you could replace this with a remark that explains where this is so, and why these DBMSs do this? -- Jan Hidders 14:08, 24 January 2006 (UTC)

Jan, it's true that several SQL-DBMSs do not support the "ON UPDATE CASCADE" part of the SQL standard, so I can probably change the paragraph into an explanation of that limitation. --Josh Berkus 1/24/06

As far as I can tell, this "immutable" constraint is FUD that Oracle emits. Oracle doesn't support "on update cascade"; other databases (DB2, SQL Server, postgres, etc.) do; it's ANSI SQL syntax and functionality. To counter this obvious minus point in their software, Oracle have established a meme that PKs should be immutable. The rationale given (a PK is an identity so should not change) sounds like nonsense. Identitifiers can change without changing the object which they denote.

OK, I've extensively re-written the article to take out the IMMUTABLE FUD. See how you like it. Note that a discussion of the pros and cons of Surrogate Keys will go in the Surrogate Key article, where it belongs. Jberkus 18:12, 23 September 2006 (UTC)

Bad examples

Using a name to look up a phone number? That's hardly unique. There must be thousands of people sharing exactly the same name. Words in a dictionary? One word can have multiple entries!

I agree. In fact, the following in the second paragraph of the introduction seems to indicate that the Dewey Decimal system is associated with identifying words:

Practical telephone books and dictionaries cannot use names or words or Dewey Decimal System numbers as candidate keys because they do not uniquely identify telephone numbers or words.

Is it possible that someone erased something from the excerpt?

--Whiteknox 03:29, 5 September 2006 (UTC)

Also, the national identification number example for a surrogate key, IMO. It works as an analogy, but the example as written isn't an analogy, and from the perspective of the database itself _both_ examples given are natural keys, with the same potential pitfalls (lack of guaranteed uniqueness, atomicity, etc.) The external semantics are irrelevant. For instance, employee numbers can be assumed or documented to be auto-incrementing, unique, and immutable integers, but if they're assigned outside the database and have external semantics, then they're a natural key. What would make it a surrogate key is if it existed purely for use as a key in the database, generated and guaranteed on its terms and no others. On the flip-side, absolute birth order—if it were knowable—would be a natural key, even though by definition it must be an auto-incrementing, unique, and immutable integer. The world and the database are two different things. --2601:140:8000:A739:75A6:1F5D:86A4:E9D9 (talk) 13:05, 10 April 2019 (UTC)

Need full SQL Standard Quotation

Hey, does anyone have a copy of the SQL92 or 99 standard they can cut & paste to put in a full defintion of how a primary key is defined? Thanks. Jberkus 18:22, 23 September 2006 (UTC)

SQL:2003 says:

<table constraint definition> ::=
    [ <constraint name definition> ] <table constraint>
    [ <constraint characteristics> ]

<table constraint> ::=
      <unique constraint definition>
    | <referential constraint definition>
    | <check constraint definition>

<unique constraint definition> ::=
    <unique specification> <left paren> <unique column list> <right paren> | UNIQUE ( VALUE )

<unique specification> ::=
      UNIQUE
    | PRIMARY KEY

<unique column list> ::= <column name list>

<column definition> ::=
    <column name> [ <data type or domain name> ]
    [ <default clause> | <identity column specification> | <generation clause> ]
    [ <column constraint definition>... ]
    [ <collate clause> ]

<column constraint definition> ::=
    [ <constraint name definition> ] <column constraint> [ <constraint characteristics> ]
<column constraint> ::=
      NOT NULL
    | <unique specification>
    | <references specification>
    | <check constraint definition>

I think it's straight-forward. You can use a separate table-element to define the PK (or a unique key). If you have a single-column key, you can alternatively add the PRIMARY KEY/UNIQUE keyword in the column definition itself. I wouldn't put the formal definition in the topic, though. --Stolze 11:09, 30 October 2006 (UTC)

Summing up

I tried to point someone to the Primary key and Foreign key pages to help them out, but the explanation isn't straightforward, so I added one sentence to sum it up. MikeyTheK 22:25, 10 February 2007 (UTC)

What you are describing is a foreign key relationship. A Primary key has many other functions than just acting as a reference point for a foreign key constraint. In addition, the first sentence of the article describes the function of a PK perfectly, and I see no reason to elaborate on this. Jerazol 09:56, 11 February 2007 (UTC)

Merging key articles

In the German section, we consolidated the database key-articles into one single article. Greetings, Fragment 19:16, 15 August 2005 (UTC)

I agree with this. A primary key is just a special case of unique keys. Thus, I applied the PK content to the unique keys article and unless someone objects, we should make this article here just a redirect. --Stolze 14:36, 14 February 2007 (UTC)

While an overall database key article is useful (and Key (database) is a better name than unique key), "primary key" is a common term, and deserves its own article. Many of the comments at Talk:Unique key ask for clarification on what a primary key is, and how it differs from a unique key. I have accordingly unmerged the articles in Special:Diff/818710398 and Special:Diff/818710464; they are now both relatively large and independent articles.

Making Unique key/Key (database) a better overview would be quite helpful, as would improving primary key specifically.

—Nils von Barth (nbarth) (talk) 04:03, 5 January 2018 (UTC)

Reverting to a separate PK article seems like a step backwards. As the article points out more than once, there is no difference between a "primary" key and any other candidate key. The candidate key and unique key articles already cover the topic - the latter could be much improved but now any useful edits have to be made in two places. Redirect PK to unique or candidate key seems to make more sense.

--Oradium (talk) 21:02, 1 March 2018 (UTC)

Removing inaccurate History section

The History section of this article currently consists entirely of the following:

Although mainly used today in the relational database context, the term "primary key" pre-dates the relational model and is also used in other database models. Charles Bachman, in his definition of the navigational database, is the first person to define primary keys.

The cited source has a publication date of 01 November 1973, but Codd had already defined and used the term "primary key" in his famous paper on the relational model, published 01 June 1970. So, the claim that Bachman originated the term, or that his usage predates the relational model, are not supported. I will delete that section of the article. - Tim314 (talk) 02:25, 28 June 2023 (UTC)

In fact, the term "primary key" used in a data retrieval context predates Codd's paper as well as Bachman's. For example, the term is used and defined in a 1963 paper in IBM Systems Journal.. That was the earliest such usage I was able to find, but I'm not confident it's the first. Tim314 (talk) 03:10, 28 June 2023 (UTC)

I found an even earlier paper by an IBM researcher (from May 1961) which defines "primary key" and "secondary key" in the context of identifying a row in a data file. The meanings given to "primary" and "secondary" are a bit different:

"If a file is in any way ordered on a given key, that key is primary and other keys are secondary."

This seems a bit more like the distinction between clustered and unclustered indexes in modern relational database terminology.

At any rate, it's clear that multiple IBM researchers were using the term "primary key" in a data storage and processing context in the early 1960s. Tim314 (talk) 04:09, 28 June 2023 (UTC)

References

Computer

Define primary key 103.47.173.15 (talk) 15:23, 27 July 2023 (UTC)