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.
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 ...
21
votes
4answers
6k views
When should a primary key be declared non-clustered?
While creating a test database for another question I asked earlier, I remembered about a Primary Key being able to be declared NONCLUSTERED
When would you use a NONCLUSTERED primary key over a ...
20
votes
7answers
2k views
Why should I create an ID column when I can use others as key fields? [duplicate]
Possible Duplicate:
Why use an int as a lookup table's primary key?
So far, I'm accustomed to creating an ID column for every table and it is practical in a way that it makes me not ...
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 ...
14
votes
3answers
958 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 ...
13
votes
2answers
634 views
Primary key index with a DATETIME as first part of the compound key is never used
I have a problem with INDEXING a DATETIME (or even a date) as first part of my PRIMARY KEY.
I use MySQL 5.5
Here are my two tables:
-- This is my standard table with dateDim as a dateTime
CREATE ...
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 ...
12
votes
5answers
372 views
Sequential GUID or bigint for 'huge' database table PK
I know this type of question comes up a lot, but I've yet to read any compelling arguments to help me make this decision. Please bear with me!
I have a huge database - it grows by about 10,000,000 ...
11
votes
5answers
586 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 ...
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?
11
votes
4answers
282 views
Best solution to fixing database design with GUID as primary key
I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.
I have a very large database (20+ million ...
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
752 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] ...
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 ...
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. ...
7
votes
3answers
473 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) ...
7
votes
4answers
243 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 ...
6
votes
2answers
286 views
Should a log table get an id field or primary key?
I have a log table that captures the datetime stamp of when certain files were exported to another system.
The exportedLog table currently has three fields:
id (primary key)
messageId ...
6
votes
2answers
1k views
What's a good way to add a PRIMARY KEY to a large InnoDB table?
I have a table with around 30 million rows in MySQL 5.5 using the InnoDB storage engine. This table has a foreign key that links to another table with around 3 million rows.
I want to add a primary ...
6
votes
3answers
135 views
Primary Key efficiency
If I need a table to hold Point of Sales transactions, and am told I need to store:
Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields
Now, ...
5
votes
3answers
687 views
Differences between “Unique Key” and “Primary Key”
What are the major differences between Unique Key and Primary Key in MySQL?
5
votes
2answers
2k views
How can you tell if a table in DB2 has a Primary Key?
We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined.
Is there a way using a select statement on the DB to see if a given table has one?
Thanks.
5
votes
3answers
752 views
Remove field from composite key and collate duplicate data
I'm looking for the best way to go about changing the primary key on a table in my DB without having to completely reinsert data from files again. The modification I'm going to make is going to ...
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 ...
5
votes
2answers
371 views
Teradata: How to design table to be normalized with many foreign key columns?
I am designing a table in Teradata with about 30 columns. These columns are going to need to store several time-interval-style values such as Daily, Monthly, Weekly, etc. It is bad design to store ...
4
votes
4answers
368 views
How much will using a varchar(15) PK affect my tables performance?
I am parsing log file lines from a legacy, proprietary application into a nice easy to query DB. The log lines have no unique integer ID. They do have a UNIX timestamp as an 8 character hex string. ...
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
2answers
1k views
How to promote an existing index to primary key in PostgreSQL
I know how to make a primary key within a table, but how do I make an existing index a primary key? I'm trying to copy an existing table from one database to another. When I show the table, the index ...
4
votes
3answers
139 views
Most efficient (or meaningful) way to index grid-based data
I have a MySQL table which I expect to be in surplus of a few million rows and 99% select statements. The problem I am having is coming up with a meaningful way to determine the Primary Key. (I have ...
4
votes
2answers
126 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
4
votes
1answer
310 views
How does SQL Server choose an index key for a foreign key reference?
I am working with a legacy database that was imported from MS Access. There are about twenty tables with non-clustered, unique primary keys that were created during the MS Access > SQL Server upgrade. ...
4
votes
1answer
586 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, ...
4
votes
4answers
732 views
Primary key should be chosen so as attributes never change?
I was reading about keys in this DatabaseSystems book -
The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person ...
3
votes
3answers
312 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
3
votes
4answers
162 views
PRIMARY KEY CONSTRAINT fundamentals
Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?
The RDBMS that is used is SQL Server.
In fact, ...
3
votes
4answers
5k views
MySQL: Why is auto_increment limited to just primary keys?
I know MySQL limits auto_increment columns to primary keys. Why is this? My first thought is that it's a performance restriction, since there probably is some counter table somewhere that must be ...
3
votes
1answer
282 views
Surrogate key / Primary key: Better to use an existing unique data field or create a key field?
I am not sure if this question has been asked or not. At least I couldn't find it.
I am curious about a primary key in terms of efficiency with data searching and retrieval.
This is a hypothetical ...
3
votes
2answers
573 views
Index on primary key not used in simple join
I have the following table and index definitions:
CREATE TABLE munkalap (
munkalap_id serial PRIMARY KEY,
...
);
CREATE TABLE munkalap_lepes (
munkalap_lepes_id serial PRIMARY KEY,
...
3
votes
3answers
511 views
Can I have create an InnoDB table without a PK? If so, would a secondary index be used as the clustered index?
Is it possible to create an InnoDB table without a Primary Key?
Since InnoDB tables are structured as clustered index around the PK, what would be the structure of a table without PK? Would that ...
3
votes
3answers
1k views
SQL table design for primary keys (best practice)
I have a User table that contains the following fields
ID (Auto-generated GUID)
UserName
Password
FirstName
LastName
Email
AddressID (GUID)
This is linked to the table Address (1-1 relation) which ...
3
votes
1answer
75 views
Optimised Schema for City-to-City Distance Table
This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relations for the data represented below. I want to scale it vertically (new rows), instead of ...
3
votes
1answer
125 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
3
votes
1answer
87 views
Should I always make an attribute id that's an primary key?
I was thinking, sometimes several columns in a table uniquely specify the whole row.
In that case, is there a need to create a special column for a primary key?
Is that good for something?
3
votes
2answers
114 views
How many fields is too much for a primary key?
I am creating a table to store information about the placement of objects in a grid (game map).
For example, I have a 10x10 grid and I want to store, in the database, that cell (5,5) has a blue box, ...
3
votes
1answer
191 views
Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning
I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
3
votes
2answers
763 views
Postgres primary key sorted in the reverse order. Will it use the index efficiently?
My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed.
My list views show records in the reverse ...
3
votes
2answers
154 views
Is index_type ignored on MySQL primary keys in create table?
I have seen a create table whose primary key had an identifier where it should have an index_type, something like that:
create table a (foo INTEGER, PRIMARY KEY foo_id (foo));
The thing is that ...
3
votes
3answers
941 views
Does the MySQL primary key affect the efficiency of a SELECT query?
Does the primary key serve any purposes apart from uniquely identifying a specific row? For example, a table with an autoincremented primary INT key could greatly aid in search due to the option of ...
