Tagged Questions

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

14
votes
3answers
955 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 ...
13
votes
5answers
3k 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 ...
9
votes
3answers
584 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 ...
11
votes
5answers
584 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 ...
12
votes
5answers
2k 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 ...
8
votes
1answer
3k 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. ...
32
votes
3answers
5k 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 ...
19
votes
3answers
2k 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 ...
15
votes
3answers
5k 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 ...
11
votes
2answers
803 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?
10
votes
4answers
1k 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
751 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
472 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
4answers
3k 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 ...
4
votes
1answer
585 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, ...
3
votes
0answers
205 views

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

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 ...
5
votes
2answers
521 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
3answers
247 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 ...