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.
0
votes
1answer
925 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.
...
9
votes
4answers
758 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
572 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
378 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 ...
3
votes
2answers
575 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,
...
0
votes
0answers
113 views
setting timestamp column as pk to prevent concurrent editing of records?
While I was familiarizing myself with all the new data types in MySQL, I noticed the TIMESTAMP data type, and thought "What if I make this TIMESTAMP field and the CustomerID field (the other pk) a ...
4
votes
3answers
141 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
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 ...
1
vote
1answer
332 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
704 views
Differences between “Unique Key” and “Primary Key”
What are the major differences between Unique Key and Primary Key in MySQL?
1
vote
1answer
683 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
365 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
108 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
85 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
904 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
567 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
127 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 ...
3
votes
1answer
463 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
2k 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 ...
2
votes
1answer
227 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 ...
2
votes
2answers
1k 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
79 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 ...
13
votes
2answers
647 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
248 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
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 ...
5
votes
2answers
529 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
454 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 ...
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 ...
3
votes
3answers
2k 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
255 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
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 ...
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 ...
23
votes
4answers
7k 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 ...
1
vote
2answers
476 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 ...
2
votes
2answers
308 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 ...
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. ...
0
votes
1answer
270 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 ...
7
votes
4answers
246 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 ...
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 ...
14
votes
3answers
976 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 ...
4
votes
1answer
593 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, ...
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 ...
1
vote
1answer
107 views
Is it require to fill all tables inside of the database?
Now,I created upper that table and in order to test I will give 3 textboxes values as below.
SqlCommand cmd = new SqlCommand
("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn ) Values
...
7
votes
3answers
479 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) ...
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.
4
votes
4answers
369 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. ...
3
votes
3answers
516 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 ...
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 ...
11
votes
5answers
595 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 ...
2
votes
1answer
383 views
Question on Design for User + User Groups + User Types + Subscriptions Model
I am redeveloping an existing web application with both weak source code and a weak underlying database design. I'm trying to develop things in a way that future expansion will be much easier, ...
