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.
3
votes
1answer
459 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 ...
3
votes
0answers
205 views
Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population
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 ...
2
votes
2answers
125 views
MySQL - Normalization over meaningless data - Compound Primay Keys or Surrogate ID field
Please forgive my title; if you have a suggestion for it, feel free to comment.
I have a database:
DROP TABLE IF EXISTS `books`;
CREATE TABLE `books` (
`isbn` VARCHAR(255) NOT NULL,
`title` ...
2
votes
3answers
247 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 ...
2
votes
2answers
451 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 ...
2
votes
4answers
555 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 ...
2
votes
1answer
893 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
293 views
Why would i get a duplicate write error of auto-generated primary key?
Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps.
What is ...
2
votes
1answer
84 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
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
2answers
306 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 ...
2
votes
3answers
357 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 ...
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 = ...
2
votes
1answer
382 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, ...
2
votes
1answer
58 views
Validate Primary Key and Index Selection
I have a table that will store transaction data from store sales registers, I have read quite a bit on index and key choice and below is what I have concluded is the best option but I am new to this ...
2
votes
1answer
220 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
43 views
What can I do to make mysql use the expected indices?
What can I do to make mysql use the expected indices?
I've got 4 tables, two containing resources, and the others containing historical changes.
One pair uses indexes correctly, the other doesn't, ...
2
votes
1answer
204 views
Unique insert performance: Primary Key vs Unique Index
I have a table of unique values (domains_unique), with collumn domain varchar(255), with more than 20 mil records.
What's the fastest way to insert into the table, by keeping the domain unique ...
2
votes
1answer
153 views
SQL Server - storing ulong as primary key
I need to store ulong values as a primary key in SQL Server. Since SQL Server doesn't have a ulong datatype (or for that matter anything unsigned), the only way to go is to have column as ...
2
votes
1answer
193 views
unique constraint violated (ORA-00001) in concurrent insert [closed]
I have a procedure that is called from concurrent transactions:
//Some actions here
INSERT INTO table1
(table1_id, table1_val1, table1_val2, table1_val3)
VALUES
...
2
votes
2answers
237 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 ...
2
votes
0answers
124 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
128 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 ...
1
vote
2answers
474 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 ...
1
vote
1answer
102 views
does last_insert_id() returns correct result when writes from multiple sessions?
I have a table in which there is a combination of 4 columns that will act as a unique key.
Instead of using a composite primary key, I want to keep an id column as the primary key. This field is on ...
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
...
1
vote
1answer
98 views
Multiple primary keys
I am new to database design and have been looking at some example data models here.
One thing I don't understand is if you have a table like client_addresses from the link above:
...
1
vote
2answers
249 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 ...
1
vote
1answer
129 views
Missing PK/FK after database migration
We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment.
Apparently, the data was transferred correctly, but the ...
1
vote
1answer
146 views
How to alter primary key constraint for 2 columns in same table in SQL Server?
In SQL Server, I already created a table called tblDatabase with some columns. I want to add a primary key on the two columns DatabaseName and Servername.
I tried to add:
ALTER TABLE tblDatabase ...
1
vote
1answer
426 views
What should one do if the auto increment primary key exceeds the maximum?
I have a primary key for a table with the following structure,
**Field** **Type**
nid int(10)unsigned
I know the maximum limit of int(10) is 4294967295. But What ...
1
vote
1answer
658 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
...
1
vote
1answer
33 views
SQL Server Key lookup Partition Pruning
I have a query inner joining multiple tables all of which are partitioned and have 10 partitions each
SELECT
A.COL1
B.COL2
C.COL3
FROM
A
INNER JOIN B ON A.ID = B.ID
INNER JOIN C ON A.ID = C.ID
...
1
vote
1answer
106 views
Is there a tuning parameter for MySQL that allows you to set an on-disk gap between non-sequential primary keys?
I understand that one of the primary issues with using a non-sequential primary key is that every disk/memory write that does not come after the last known key causes a re-write of everything from the ...
1
vote
1answer
65 views
Can I place the attribute in the Entity, when the foreign key is also already present?
I am using DBdesigner Fork for generating ER diagram.
I have the 'Company' as an entity for which can I place the attribute of 'idShares', despite of having the foreign key of Shares Entity in the ...
1
vote
1answer
331 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 ...
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 ...
1
vote
1answer
104 views
Are these custom module Drupal tables lacking foreign keys? mysqldump with table structures without data is included
I am troubleshooting a some tables in MySQL used by a custom Drupal module. Documentation for these tables is poor so I used mysqldump to get the structures in the Drupal database without the data. I ...
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 ...
1
vote
5answers
546 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 ...
0
votes
1answer
109 views
Maintain table with too many insert
I have to create a table. Its size is going to increase very fast.
Is there going to be any issue if many insert queries are fired at the same instant as mentioned here - User autoincrement ID for ...
0
votes
1answer
167 views
The four properties of a primary key
In our dbms class, these were the four properties of a primary key discussed for Oracle.
Unique
Not NULL
Fully functional dependency
Indexed
I understand all the properties except the 3rd one ...
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 ...
0
votes
1answer
19 views
Which of tables key to reference?
MySQL.
There is a table users with autoincrement primary key id.
There is also a table employees (every employee is a user but not vice versa). The employees primary key is userid. The field userid ...
0
votes
1answer
152 views
MySQL non-sequential primary key of certain length
what's the best way to create a non-sequential primary key of a certain length in MySQL?
I'm looking to use the primary key as a 'Pin' number which real actual human people can use to refer to the ...
0
votes
1answer
25 views
Modify legacy table schemas that have no primary key
I've been tasked with updating our database (SQL Server 2008) so that every table in it has a proper primary key defined. Due to various reasons over the years we have somehow built up some legacy ...
0
votes
1answer
49 views
Retrieving all PK and FK with pgadmin
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?
0
votes
1answer
141 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 ...
0
votes
1answer
916 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.
...
0
votes
1answer
107 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 ...
