Questions tagged [primary-key]

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.

Filter by
Sorted by
Tagged with
74
votes
5answers
27k 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 ...
33
votes
3answers
5k 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 '...
30
votes
5answers
8k 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 ...
170
votes
5answers
116k 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 as opposed ...
17
votes
4answers
14k views

Retrieving all PK and FK

I have a big database that I need to extract all primary keys and foreign keys from each table. I have pgAdmin III. Is there a way to do this automatically and not go over each table manually?
19
votes
5answers
2k 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 ...
13
votes
4answers
21k 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 ...
14
votes
2answers
5k views

What are the best practices regarding lookup tables in relational databases?

Lookup tables (or code tables, as some people call them) are usually a collection of the possible values that can be given for a certain column. For example, suppose we have a lookup table called ...
3
votes
1answer
5k views

PostgreSQL SELECT primary key as “serial” or “bigserial”

I've meshed together a way to determine what the data_type is as in the data_type you use in the syntax when creating a new table based off of the PostgreSQL wiki page. If there is something wrong ...
9
votes
3answers
719 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 ...
25
votes
1answer
15k 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
4answers
9k views

How can I convert a key in a SQL Server deadlock report to the value?

I have a deadlock report that tells me that there was a conflict involving waitresource="KEY: 9:72057632651542528 (543066506c7c)" and I can see this: <keylock hobtid="72057632651542528" dbid="9" ...
17
votes
2answers
1k 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?
2
votes
1answer
5k views

Unable to drop non-PK index because it is referenced in a foreign key constraint

I have a table named MyTable. The primary key is an identity int column named MyTableID. There is a unique clustered index on the PK column MyTableID named PK_MyTable. I noticed there is an ...
0
votes
2answers
423 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 ...
97
votes
7answers
68k 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 ...
49
votes
7answers
38k 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 think ...
17
votes
4answers
28k views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My ...
10
votes
1answer
15k 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 ...
8
votes
2answers
16k 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 ...
2
votes
1answer
1k views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
2
votes
3answers
4k views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
15
votes
4answers
2k views

Why should a key be made explicit?

I am very new to the subject of databases so this may sound ignorant, but I am curious why a key should be made explicit within a table. Is this primarily to tell the user that the given column value ...
8
votes
1answer
2k views

Compacting a sequence in PostgreSQL

I have a id serial PRIMARY KEY column in a PostgreSQL table. Many ids are missing because I have deleted the corresponding row. Now I want to "compact" the table by restarting the sequence and ...
5
votes
1answer
1k views

Designing a database structure for companies and stock owners

I'm working on a personal project where I want to make a database that holds the information for every owner of every public company. Say for example "Sears INC" and the program would get the ...
10
votes
3answers
1k views

How do I find all tables in a DB that have no explicit primary key?

A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as ...
61
votes
3answers
37k 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 ...
30
votes
3answers
8k 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 ...
14
votes
5answers
5k 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 ...
13
votes
5answers
13k views

Sharing a single primary key sequence across a database?

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it ...
5
votes
1answer
646 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
17
votes
4answers
9k 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 ...
3
votes
2answers
23k views

Update datatype of primary key without deleting the table [closed]

I have created a table State which my other table, Addresses, references. CREATE TABLE State ( StateId varchar(20) PRIMARY KEY NOT NULL, Text varchar(40) NOT NULL ) I want to change its ...
12
votes
3answers
6k 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) ...
15
votes
4answers
13k 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
2answers
2k 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. ...
6
votes
2answers
451 views

Should I avoid adding a clustered key to a table with high throughput

We have a SQL Server solution that has a table dsStaging.Audit that stores audit records created by a third party transactional database. We use these audits to synchronise CRUD operations from the ...
6
votes
2answers
1k 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, (...
6
votes
1answer
3k 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, ...
5
votes
2answers
3k views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
4
votes
3answers
5k views

SQL table design for primary keys (best practice)

I have a User table that is made up of the the following columns: ID (Auto-generated GUID) UserName Password FirstName LastName Email AddressID (GUID) This is linked to the table Address (in a one-...
4
votes
1answer
4k 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 ...
3
votes
2answers
1k views

What and How is the ID antipattern?

What is the ID antipattern and why / under which circumstances is it considered an antipattern? The id antipattern is having a unique ID column in each table. The ID is generated for each new ...
3
votes
1answer
3k views

Partitioning Query

I have a couple of questions about the phyiscal layout of tables when they are partitioned. I've been researching this but am still a little unsure. Say I have an existing table:- CREATE TABLE ...
2
votes
3answers
2k 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
85 views

# of key locks while updating

I am using SQL Server 2008, RTM. I have a simple table defined as create table t(id integer not null primary key) This table has 50,000 records, just in case. When I issue an update transaction ...
0
votes
1answer
334 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 ...
7
votes
2answers
14k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
6
votes
4answers
1k views

Primary Key choice on table with unique identifier

I'm reconsidering my choice of Primary Key on a table, and would like some input. Some context: We are building a REST API, where clients access "Assets" identified by a GUID. So, the table for ...
5
votes
2answers
221 views

What could justify a non explicit - non atomic primary key

I'm working as a "guest" dev at a random company. I'm working with a table that does not have a primary key by definition(Oracle). The column named "SOMETHING_ID" is varchar2(10) and constructed at ...