Database Administrators Stack Exchange
Database Administrators Stack Exchange

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.

653 questions
208
votes
6 answers

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 to a CLUSTERED primary key? Thanks in advance
Stuart Blackler
  • 4,540
  • 7
  • 30
  • 43
177
votes
6 answers

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 unique. In which case a Guid would be better than and…
BrunoLM
  • 3,533
  • 7
  • 28
  • 22
96
votes
5 answers

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 user table Are these equivalent? If not then why?…
codecool
  • 2,023
  • 2
  • 17
  • 22
75
votes
3 answers

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 design since the UUID/GUID is quite large and they…
Jonas
  • 33,945
  • 27
  • 62
  • 64
57
votes
7 answers

Why should I create an ID column when I can use others as key fields?

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 about decision making about primary key theories. The…
Uğur Gümüşhan
  • 717
  • 2
  • 6
  • 12
51
votes
7 answers

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 Postgres database does in fact have a myapp_mymodel object…
orokusaki
  • 1,209
  • 2
  • 12
  • 21
43
votes
3 answers

Are composite primary keys bad practice?

I want to know if composite primary keys are bad practice and if not, in which scenarios is their use beneficial? My question is based on this article Note the part about composite primary keys: Bad Practice No. 6: Composite Primary Keys This is…
hackvan
  • 557
  • 1
  • 4
  • 7
41
votes
1 answer

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references through a FK to the second table's primary key. On…
stUrb
  • 757
  • 2
  • 9
  • 15
40
votes
3 answers

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 'artificial'. However, it sometimes seems to be a…
Jack Douglas
  • 40,517
  • 16
  • 106
  • 178
38
votes
2 answers

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever loaded - its a historical record (SQL Standard,…
ImmortalStrawberry
  • 867
  • 6
  • 12
  • 18
34
votes
4 answers

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?
Nick Ginanto
  • 1,009
  • 3
  • 10
  • 10
33
votes
1 answer

How to set up multiple fields as primary key in MySQL?

I have a table with fields EmployeeID blahblah blahblah2 ..... RecordMonth RecordYear so each employee should only have a matching entry for a month, year, Emp#. How do I set up a table. So how do I set up the table so that EmployeeID can be…
LOSTinDB
  • 551
  • 2
  • 5
  • 16
32
votes
2 answers

Multiple primary keys in PostgreSQL

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD CONSTRAINT "ID_PKEY" PRIMARY KEY (word,id); When I…
mostafa
  • 339
  • 1
  • 3
  • 4
31
votes
3 answers

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 auto-incrementing integer so that when I store…
BenV
  • 4,923
  • 7
  • 40
  • 38
31
votes
5 answers

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 nvarchar(50) rather than an int would use way more…
Jaco Briers
  • 413
  • 1
  • 4
  • 6
1
2 3
…
43 44