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

2
votes
2answers
468 views

Replacing composite key with surrogate

I have the following table that has a couple of million rows in it and is 99% fragmented virtually all of the time. My plan was to insert a IDENTITY field as a surrogate key to replace the current ...
3
votes
1answer
401 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 ...
1
vote
1answer
649 views

DynamoDB: searching with primary key or range key, which way is faster?

To those familiar with DynamoDB, I'm trying to make a table where items only have 2 entries. For example, . Most of the time I'll be searching the user_id with a facebook_id on hand. Amazon's ...
3
votes
1answer
486 views

How can I manage Primary Key length limit of 900 bytes when using hirarchyid 2 columns?

I have to define a Primary key on a table which has following 4 columns and its data types. GSiteID hirarchyid,, SiteID varchar(10), GComID hirarchyid, ComID nvarchar(10) As we know there is limit ...
1
vote
2answers
156 views

How do I create a rule for a Primary Key

I am using SQL Server 2005 and I need to create a primary key with an alpha character and 3 numbers together: p001 b201 I know I need to create a rule and bind that rule to the primary ...
2
votes
3answers
2k 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 ...
4
votes
1answer
386 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 ...
4
votes
3answers
1k 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 ...
2
votes
0answers
365 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 ...
4
votes
1answer
953 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. ...
3
votes
2answers
2k 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 ...
0
votes
2answers
3k views

How can primary keys in a Netezza table be indexed?

What is the best way to index a Netezza fact table with lots of primary keys. For now, let's suppose we have 10 primary keys. This table is mainly used to store meta data about our database. ...
10
votes
4answers
3k 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] ...
1
vote
5answers
2k views

How should I save apache logs into a mysql table?

This is a php script to view apache logs. I want to save apache logs into a mysql database. Then add some rules for tagging urls using mysql REGEXP search, like: SET tag='some tag' WHERE url REGEXP ...
5
votes
2answers
815 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 ...
5
votes
2answers
2k 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, ...
4
votes
3answers
227 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 ...
10
votes
4answers
9k 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 ...
1
vote
1answer
551 views

SQL - double column used from primary key with autoincrement

I am user the following as primary key in the SqlDB: I am doing the Index as Autoincrement, but it is autoincremented in general not per user. Example userid=1,index=1, then insert another for ...
5
votes
3answers
4k views

Differences between “Unique Key” and “Primary Key”

What are the major differences between Unique Key and Primary Key in MySQL?
1
vote
1answer
2k views

Does the order of keys in compound Primary key make any difference?

Package Package ------- PackageID PackageName CityID PRIMARY KEY (PackageID) UNIQUE KEY (PackageName) FOREIGN KEY (CityID) REFERENCES City (CityID) Package versions: PackageVersion ...
2
votes
3answers
1k views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
0
votes
1answer
172 views

Impact of miss-ordered fields in a composite, clustered primary key?

I have tables where the primary key is something like this: -StoreID -BusinessDate Typically, there is one row for every store (~100) for every single day. One row for each store is inserted on a ...
2
votes
1answer
141 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
2
votes
1answer
3k views

How is INDEX on Composite Primary Key in mysql?

When making a composite primary key for two or more columns, e.g. PRIMARY KEY(col1, col2, col3); will the system INDEX each column individually? The reason that I am asking this question is that when ...
2
votes
4answers
2k views

Two-sided UNIQUE INDEX for two columns

Creating a table with composite PRIMARY KEY or using UNIQUE INDEX for two columns guarantee uniqueness of col1, col2. Is there a tricky approach to make the reverse order of two columns UNIQUE too ...
1
vote
2answers
176 views

Update primary key

I want to update a primary key column, but the change should cascade to all other child tables where this primary key is used as foreign key. Example: table1 has column "slug" as primary key. One row ...
4
votes
1answer
917 views

Primary Key Type's Effect on Performance in Oracle?

In MS SQL Server, it is well documented that the type of the primary key can have a dramatic effect on performance. Using narrower types and sequential values significantly improves insert performance ...
2
votes
2answers
3k views

Single Identity column and composite key, which to make primary?

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work ...
1
vote
1answer
1k views

updating mysql primary keys

I need to be able to swap values that are used in a primary key. Meaning, in one update statement, I need to change id 1 to id 2, and id 2 to id 1. In other relational databases, (Sql Server and ...
3
votes
2answers
3k views

“on duplicate key update” all the non-key fields mentioned in the insert

How can I update the field values mentioned in the insert portion without listing them again in the on duplicate key update portion? insert into tablename set keyname = 'keyvalue', fieldname = ...
1
vote
0answers
80 views

Is it possible to change the data-type of a primary key column in SQL Azure? [duplicate]

Possible Duplicate: How can I alter an existing Primary Key on SQL Azure? OK, so say I have a table: 'Contact' and a primary key: 'ContactID'. The primary key is an INT but I want to ...
15
votes
2answers
2k 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 ...
2
votes
3answers
480 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 ...
3
votes
2answers
296 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 ...
5
votes
2answers
1k 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
2answers
612 views

Generalisation in databases

I am working on a Hospital Management project in DB2. I have one table for staff with a primary key of staff_id. The hospital has to have different staff... e.g doctors,nurses,e.t.c. Is it better to ...
6
votes
2answers
4k 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 ...
3
votes
3answers
3k 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 ...
1
vote
2answers
538 views

serial field issue

Q: I use a serial as a datatype for my primary keys in my tables. I insert a lot of data then truncate these data one after one many times to test the data entry. My question is: Is there any ...
9
votes
3answers
649 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 ...
24
votes
7answers
5k 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 ...
42
votes
4answers
24k 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 ...
2
votes
2answers
984 views

How can an identity primary key index become fragmented?

From what I understand about index fragmentation, this should not be possible. The cases I have found in my databases are non-clustered. Example: ALTER TABLE [dbo].[ClaimLineInstitutional] ADD ...
4
votes
3answers
511 views

What are the pros and cons of using my customer codes as a primary key?

I am designing a database for one of my sales teams. Right now, the goal is to get a master list of customers and their mailing addresses, but the DB will eventually expand to track some other ...
16
votes
1answer
8k 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. ...
0
votes
1answer
345 views

How SQLite index ID (or line number)

SQLite is based on a flatfile, and store virtual tables in a flatfile (I think based on offset, correct me if I'm wrong). But how it index the Primary Key (ID), which I think is identical to line ...
8
votes
4answers
397 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 ...
25
votes
5answers
7k 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 ...