Databases Start‑class ( inactive) | |||||||
|
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)
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
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!
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)
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)
<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 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)
In the German section, we consolidated the database key-articles into one single article. Greetings, Fragment 19:16, 15 August 2005 (UTC)
The History section of this article currently consists entirely of the following:
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 [2], 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)
References
Define primary key 103.47.173.15 ( talk) 15:23, 27 July 2023 (UTC)
Databases Start‑class ( inactive) | |||||||
|
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)
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
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!
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)
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)
<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 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)
In the German section, we consolidated the database key-articles into one single article. Greetings, Fragment 19:16, 15 August 2005 (UTC)
The History section of this article currently consists entirely of the following:
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 [2], 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)
References
Define primary key 103.47.173.15 ( talk) 15:23, 27 July 2023 (UTC)