Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Why do I need to have a primary key on my database for it to function correctly? In every tutorial I read, you need to make the id key the primary key. What does the primary key do differently than the regular cells?

share|improve this question
3  
Do you know what a primary key is? –  gbn Dec 16 '11 at 12:59
1  
Something the answers don't make explicit is that you do not need a primary key to function correctly. PKs are vital for integrity and often helpful for performance, but ordinary data manipulation will work fine without them. That doesn't mean you shouldn't have them, however. –  Ben Brocka Dec 16 '11 at 17:05
    
@BenBrocka: A table without any unique constraint, can have duplicate rows. Then, it's not a table anymore (despite that most SQL products will allow you to have such a monster). –  ypercube Dec 16 '11 at 21:14
    
@ypercube true, but that's not exclusive functionality to keys (unique constraints and indexes can fix that too), plus the point is everything still works. Whether it should work is another argument :) –  Ben Brocka Dec 16 '11 at 21:32
1  
@BenBrocka, In SQL DBMSs many operations are difficult or impossible unless you can uniquely identify rows. Whole features may be disabled or crippled without keys. I don't see how you can justify a claim that the "ordinary data manipulation will work fine" without keys. I think there's plenty of evidence that lack of keys and the problem of duplicate rows cost vast amounts of time and money for database customers to fix. The fact that keys and uniqueness are not automatically enforced is the most serious flaw of SQL-based systems and the most difficult problem to solve. –  sqlvogel Dec 17 '11 at 12:45
show 3 more comments

2 Answers

up vote 5 down vote accepted

Keys are for identification and data integrity. A key defines how tuples (rows) in a table can be uniquely identified. The integrity of keys is assured because the DBMS prevents users from entering duplicate information into the table. Database users can therefore rely on the keys to identify in the real world the things recorded in the database.

A "primary" key is fundamentally no different from any other candidate key in the same table. It's just a convention used to designate one key per table as significant in some way. Usually it is the "preferred" or "most important" key of a table and the one used in foreign key references.

share|improve this answer
2  
"users can therefore rely on the keys to identify in the real world the things recorded in the database" -- but is that the true of an "id key" (auto-increment) e.g. the second table in my answer? –  onedaywhen Dec 16 '11 at 14:07
add comment

You allude to tutorials but provide no example. Consider the following two tables that have what I understand to be an "id key" (noting the question has the auto-increment tag) and similar to many found in the wild:

CREATE TABLE Books1
(
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 isbn CHAR(13) NOT NULL
);

INSERT INTO Books1 (isbn) VALUES ('9781444727302'), 
                                 ('9781444727302'),
                                 ('9781444727302');


CREATE TABLE Books2
(
 id INTEGER NOT NULL PRIMARY KEY AUTO_INCREMENT, 
 isbn CHAR(13)
);

INSERT INTO Books2 (isbn) VALUES (NULL), 
                                 (NULL),
                                 (NULL);

Does either table have a usable key? I would say no. So my first answer is that PRIMARY KEY (or auto-increment) is like any tool: without discipline it can be useless or even dangerous.

--

Reading between the lines, I think the question effectively asks

The tutorials I read tell me to add an auto-increment column to every table; they also tell me every table should have a PRIMARY KEY. Why doesn't the DBMS just automatically add the auto-increment column and make it the PRIMARY KEY?

or perhaps

The tutorials I read tell me to add an auto-increment column to every table. Isn't a system auto-increment column unique by implication? If so, why do I have to tell the system it is a PRIMARY KEY?

to which my second answer is, seek out a better tutorial!

share|improve this answer
    
That would be a good example of a surrogate key. Normally you would also define the ISBN column as an alternate key (make it NOT NULL, and put a unique index/constraint on it). In SQL Server at least, the use of a surrogate key as the primary key can be a good idea for clustered indexes, data warehousing, etc. –  db2 Dec 16 '11 at 14:07
    
@db2: Did you really mean to to say "good"?! –  onedaywhen Dec 16 '11 at 14:12
1  
...and if the table has no other key, for what is it a surrogate? –  onedaywhen Dec 16 '11 at 14:19
1  
@db2: I don't think id is a surrogate key at all: there is no alternate key for which id is the surrogate. –  onedaywhen Dec 16 '11 at 15:17
2  
@db2. Oh if only the ISBN key could have a unique constraint on it. If only publishers were that well behaved. –  TRiG Dec 16 '11 at 17:51
show 9 more comments

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.