Hot answers tagged primary-key
36
The question is not 'when should the PK be NC', but instead you should ask 'what is the proper key for the clustered index'?
And the answer really depends on how do you query the data. The clustered index has an advantage over all other indexes: since it always includes all columns, is always covering. Therefore queries that can leverage the clustered index ...
33
This has been asked in SO here and here
Jeff's this post will explain alot about pros and cos of using GUID.
GUID Pros
Unique across every table,every database, every server
Allows easy merging of records from different databases
Allows easy distribution of databases across multiple servers
You can generate IDs anywhere, instead of ...
23
1 - It's faster. A JOIN on an integer is much quicker than a JOIN on a string field or combination of fields. It's more efficient to compare integers than strings.
2 - It's simpler. It's much easier to map relations based on a single numeric field than on a combination of other fields of varying data types.
3 - It's data-independent. If you match on ...
16
It Depends on your engine. Common wisdom is that reads are cheap, a few bytes here and there will not significantly impact the performance of a small to medium size database.
More importantly, it depends on the uses to which you will put the primary key. Integer serials have the advantage of being simple to use and implement. They also, depending on the ...
16
The is no such thing as a 'primary index'. There is such a thing as a 'primary key' and also there is such a thing as a 'clustered index'. Distinct concepts, often confused. With this distinction in mind, lets revisit the question:
Q1) Can the clustered index in a SQL Azure table be modified?
A: Yes. Use WITH (DROP_EXISTING=ON):
create table Friend (
...
14
Primary keys (and other unique constraints) are implemented as indexes, and are dealt with in exactly the same way - it doesn't make sense from the programmer's point of view to have separate code paths for PKs and indexes (it would double up the potential for bugs).
Other than being referred to by foreign keys, a PK is just a unique constraint which is in ...
14
I'm going to say no, not always, but most of the time yes..
These are some circumstances in which you don't need a surrogate or artificial key:
Pure intersection tables. If there is no risk of the
intersection being the target of a foreign key and if there is little
or no risk of the intersection attracting independent attributes
(i.e. something other ...
14
Here are the results of querying a table on the second column of a multicolumn index.
The effects are easy to reproduce for anybody. Just try it at home.
I tested with PostgreSQL 9.0:
event=# SELECT version();
version
-------------------------------------------------------------------------------------------
...
13
The answer to your question is logical, not physical - the value you look up might change for business reasons. For example, if you index your customers by email address, what happens when an email address changes? Obviously this won't apply to all your lookup tables, but the benefits of doing it the same way across the entire application is that it makes ...
13
If you're synchronizing your data with an external source, a persistent GUID can be much better. A quick example of where we're using a GUIDs is a tool that is sent to the customer to crawl their network and do certain classes of auto-discovery, store the records find, and then all the customer records are integrated into a central database back on our end. ...
11
One main difference is that the unique index can have a NULL value that is not allowed in the primary key. Clustered or not, this is the main difference between the practical implementation of a Primary Key versus a Unique Key.
Oh, and the fact that a table can have one PK and many UK :-).
These are both differences in INTENT not in PERFORMANCE.
Otherwise, ...
11
re 1) Yes and no.
For a query that uses both columns e.g. where (user_id1, user_id2) = (1,2) it doesn't matter which index is created.
For a query that has a condition on only one of the columns e.g. where user_id1 = 1 it does matter because usually only the "leading" columns can be used for a comparison by the optimizer. So where user_id1 = 1 would be ...
10
I believe the primary (good) reason is:
Because people have learned from experience that not doing so leads to problems.
I've developed database applications for 20 years. Most critically I spent five years working with data warehouses. In the early days choosing another field seemed ok. Then we found duplicate records, sometimes unique validations were ...
10
For a non partitioned table I get the following plan
There is a single seek predicate on Seek Keys[1]: Prefix: DeviceId, SensorId = (3819, 53), Start: Date < 1339225010.
Meaning that SQL Server can perform an equality seek on the first two columns and then begin a range seek starting at 1339225010 and ordered FORWARD (as the index is defined with ...
10
The distinction is mainly historical. The relational model was developed in part in response to the way IMS handled data.
The presently released version of IMS provides the user with a choice for each file: a choice between no indexing at all (the hierarchic sequential organization) or indexing on the primary key only . . .
Source: A Relational Model ...
9
In your case these fields are natural key.
Surrogate Key:
Surrogate keys are keys that have no
“business” meaning and are solely used
to identify a record in the table.
Such keys are either database
generated (example: Identity in SQL
Server, Sequence in Oracle,
Sequence/Identity in DB2 UDB etc.) or
system generated values (like
...
9
If you want to lookup your data, you really want to do this based on an integer field or fields. This is why many people use an ID field for this.
But if you have a table you use for a many-to-many relation, it isn't really needed.
Lets say you have the following two tables:
Table news
id integer
title varchar
item text
Table tags
id integer
name varchar
...
9
You probably don't want to use rules and instead use constraints, in this case a check constraint. The reason that you don't want to use rules is that rules have been deprecated means that they will be removed from SQL Server at some point in the future so it would be better to use the check constraint instead. Something like this will do the trick.
...
9
You should never assume that a data point which is outside of the control of your system will never change. This means you shouldn't assume student names won't change. There are lots of reasons in the real world why names might change. Anything that is at reasonable risk of changing is a bad candidate for a primary key. Also, names are very unlikely to ...
9
Email is a particularly bad choice for any PK whether composite or single. See my answer on this question on Stack Overflow for why:
http://stackoverflow.com/questions/3804108/is-email-address-a-bad-primary-key/3804174#3804174
8
It depends on your generation function and size of the final tables
GUIDs are intended to be globally unique identifiers. As discussed in the Postgres 8.3 documentation there are no methodologies that are universally appropriate to generate these identifiers, but postgreSQL does ship with a few more useful candidates.
From the scope of your problem, and ...
8
A table can have at most one PRIMARY KEY constraint but it can have as many as you want UNIQUE KEY constraints.
Columns that are part of the PRIMARY KEY must be defined as NOT NULL. That is not required for columns that are part of UNIQUE KEY constraints. If the columns are not Nullable, then there is no difference between Unique and Primary Keys.
Another ...
7
All a primary key is is a value that we have determined is the value that is of utmost importance in a record. Whether that key is a signed int, an unsigned int, a string, a blob (actually, there are limits) or a UUID (or whatever name it takes today), the fact still stands that it is a key, and that it is the thing of utmost importance.
Since we're not ...
7
If we're on about identity or autonumber columns, the value itself should have no meaning. (sometimes it does, as per SO's chat users nentioned by drachenstern, which I've done before myself)
However, generally you'd lose half of your range if you're using signed integers.
See: What to do when a field in a table approaches the max signed or unsigned 32 bit ...
7
Not all database systems even support unsigned integer types, MSSQL being one of those that doesn't. In these cases negative values are possible in integer key fields simply because they are possible in the type (you could use rules or triggers to block them, as shown in this example, but there is probably no need add the overhead of enforcing such rules to ...
7
"it depends"
Yes: Surrogate IDENTITY/AUTONUMBER fields are good when the natural key is wide and non-numeric. Note: this assumes the conflation of "PK" and clustered index that occurs by default in SQL Server and Sybase etc
No: many/many tables when the 2 parent keys suffice. Or when the natural key is short and fixed length eg currency code
Of course, a ...
7
What are the performance considerations between using a broad PK vs a separate synthetic key and UQ?
There is no significant disadvantage using the natural key as the clustered index
there are no non-clustered indexes
no foreign keys referencing this table (it is a parent row)
The downside would be increased page splits as data inserts would be distributed throughout the data, instead of at the end.
Where you do have FKs or NC indexes, the using a ...
7
The basic reason to use Clustered indexes is stated on Wikipedia:
Clustering alters the data block into a certain distinct order to match the index, resulting in the row data being stored in order. Therefore, only one clustered index can be created on a given database table. Clustered indices can greatly increase overall speed of retrieval, but usually ...
7
Three things leap out:
Your DELETE is on the 2nd column (RespondentID) of the current PK which means a scan, not a seek.
Pointless ROWLOCK hint
Your "UPSERT" pattern is not concurrency safe. The test for existence may pass for 2 overlapping (in time) concurrent threads giving an error.
To fix
Reverse your PK order in DEXTable to (RespondentID, ...
7
First of all, if this is homework, please tag it as such.
Secondly if it's not homework and you're doing this in a professional environment, get a professional to do it (or at least to thoroughly scrutinize your final design). Schema design underpins your application design, and flows on from clarity in business requirements and how well you understand ...
Only top voted, non community-wiki answers of a minimum length are eligible
