In relational database design, a primary key can uniquely identify each row in a table. A primary key comprises a single column or a set of columns.

learn more… | top users | synonyms

23
votes
5answers
6k views

Working of indexes in PostgreSQL

I have a couple of questions regarding working of indexes in PostgreSQL. I have a Friends table with the following index: Friends ( user_id1 ,user_id2) user_id1 and user_id2 are foreign keys to ...
17
votes
3answers
2k views

Should every table have a single-field surrogate/artificial primary key?

I understand one benefit of surrogate/artificial keys in general - they do not change and that can be very convenient. This is true whether they are single or multiple field - as long as they are ...
9
votes
3answers
638 views

Need Help Troubleshooting Sql Server 2005 Deadlock Scenario

I am running into a deadlock scenario where the only participants in the deadlock appear to be a single table and a single stored procedure that deletes from that table. I drew that conclusion based ...
15
votes
1answer
6k views

How can I alter an existing Primary Key on SQL Azure?

I want to modify an existing primary key on a SQL Azure table. It currently has one column, and I want to add another. Now, on SQL Server 2008 this was a piece of cake, just did it in SSMS, poof. ...
15
votes
5answers
3k views

Why use an int as a lookup table's primary key?

I want to know why I should use an int as a lookup table's primary key instead of just using the lookup value as the primary key (which in most cases would be a string). I understand that using a ...
13
votes
5answers
866 views

Why do primary keys have names of their own?

From a mathematical view, granted that a table has at most one primary key, it seems to be a shortsighted design decision to refer to primary keys by some arbitrary name instead of a simple table ...
9
votes
4answers
8k views

Mysql int vs varchar as primary key (InnoDB Storage Engine?

I am build a web application (project management system) and I have been wondering about this when it come to performance. I have an Issues table an inside it there are 12 foreign keys linking to ...
0
votes
2answers
106 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
12
votes
2answers
996 views

Is there any tangible difference between a unique clustered index and a clustered primary key?

I understand that there may be a difference in meaning or intent between the two, but are there any behavioral or performance differences between a clustered primary key and a clustered unique index?
0
votes
2answers
3k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
39
votes
3answers
8k views

Guid vs INT - Which is better as a primary key?

I've being reading around reasons to use or not Guid and int. int is smaller, faster, easy to remember, keeps a chronological sequence. And as for Guid, the only advantage I found is that it is ...
20
votes
3answers
3k views

Character vs Integer primary keys

I'm designing a database with multiple lookup tables containing possible attributes of the main entities. I'm thinking of using a 4 or 5-character key to identify these lookup values rather than an ...
22
votes
3answers
12k views

What are the drawbacks with using UUID or GUID as a primary key?

I would like to build a distributed system. I need to store data in databases and it would be helpful to use an UUID or a GUID as a primary key on some tables. I assume it's a drawbacks with this ...
10
votes
4answers
3k views

Is there any benefit of a primary key that comprises all columns of the table?

I have a table with four columns that are all non-nullable, and the data is such that all four are needed to distinguish a unique record. This means that if I were to make a primary key, it would need ...
9
votes
4answers
2k views

Sort order specified in primary key, yet sorting is executed on SELECT

I'm storing sensor data in a table SensorValues. The table and primary key is as follows: CREATE TABLE [dbo].[SensorValues]( [DeviceId] [int] NOT NULL, [SensorId] [int] NOT NULL, [SensorValue] ...
7
votes
3answers
989 views

What are negative keys used for?

Somewhat new to using standard SQL databases (currently working with MySQL mostly) I haven't run across many usages of this as of yet. When and why is it useful to have negative (or rather signed) ...
4
votes
1answer
1k views

PostgreSQL: Corrupt primary key, inconsistent table

While recovering from a cloud failure, I found that some tables on a PostgreSQL database are behaving strangely. These tables are indexed using a primary key, but a pg_dump yielded duplicate fields, ...
2
votes
0answers
340 views

Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population [closed]

When I create a temporary table with a PK defined from the outset like this I get great performance in subsequent joins on that key: create table #temp ( field1 int not null field2 int ...
8
votes
4answers
355 views

What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?

I have several tables where records can be uniquely identified with several broad business fields. In the past, I've used these fields as a PK, with these benefits in mind: Simplicity; there are no ...
5
votes
2answers
992 views

What Is The Point of a Primary Key?

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 ...
2
votes
1answer
326 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
2
votes
3answers
428 views

File name as primary key?

I have a few tables I'm going to create for a website, Literature, video, and image. Is there a good reason why I shouldn't use the file name as a primary key. For instance... "someimage.png" as the ...
1
vote
1answer
173 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
0
votes
1answer
143 views

Relational database primary key requirements

I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...