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
2answers
44 views
checksum(newid()) as primary key - what happens if a collision occurs?
Inspecting a rather critical database that is used by some software on my system, I found that one of the tables had a primary key on the column Id, where Id is calculated using checksum(newid()). ...
0
votes
0answers
10 views
Would Mongo's ObjectID work well in InnoDB's clustered index?
Mongo's ObjectID is defined like this:
ObjectId is a 12-byte BSON type, constructed using:
a 4-byte value representing the seconds since the Unix epoch,
a 3-byte machine identifier,
...
1
vote
1answer
41 views
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
...
0
votes
1answer
25 views
Candidate key = Key = Minimal Superkey?
I got a little confused by all these keys. As I understand
A key of a relational schema =
The minimal subset of a superkey that is still a key =
A candidate key
Is this correct or not?
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 ...
0
votes
2answers
48 views
Can a surrogate key and a primary key be in the same table?
For example we have:
Surrogate key: ABC123
Primary key: 1
Name: James
Is this legit in a Data Warehouse table?
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 ...
0
votes
0answers
41 views
is it good to use combined primary key for MySQL InnoDB clustered index?
I'm trying to build a aggregated reader website of multiple forums.
Because most queries are likely to be within same time periods for written_time column,
I'm thinking about taking advantage of ...
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, ...
0
votes
0answers
32 views
Postgresql Retrieve / Find Primary Key Serial Autonumber field for Audit logs
I need to find the Primary Key field name in each of a number (approx 70) of tables to create audit logs.
All the primary keys are type serial (auto incrementing integer) and all have the name in the ...
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 ...
2
votes
1answer
61 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 ...
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 ...
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 ...
2
votes
2answers
126 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` ...
6
votes
3answers
136 views
Primary Key efficiency
If I need a table to hold Point of Sales transactions, and am told I need to store:
Country ID
Store Number
POS Terminal Number
Transaction Date
Item Code
Teller ID
Another Field
More Fields
Now, ...
11
votes
4answers
290 views
Best solution to fixing database design with GUID as primary key
I am after some confirmation of this idea to fix a badly performing database or a better suggestion if any one has one. Always open to better suggestions.
I have a very large database (20+ million ...
19
votes
3answers
2k 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 ...
0
votes
1answer
52 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?
3
votes
4answers
166 views
PRIMARY KEY CONSTRAINT fundamentals
Can you explain why "primary key constraint" exists if, as you know, it is possible to create relationships between tables using unique indexes only ?
The RDBMS that is used is SQL Server.
In fact, ...
2
votes
1answer
212 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
...
3
votes
1answer
88 views
Should I always make an attribute id that's an primary key?
I was thinking, sometimes several columns in a table uniquely specify the whole row.
In that case, is there a need to create a special column for a primary key?
Is that good for something?
1
vote
1answer
108 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
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 ...
1
vote
1answer
133 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
99 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:
...
2
votes
1answer
220 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 ...
0
votes
1answer
143 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 ...
2
votes
2answers
240 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 ...
1
vote
5answers
564 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 ...
2
votes
1answer
156 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 ...
3
votes
1answer
127 views
Index Key Column Order
I have created a joining table for many-to-many relationship.
The table only has 2 cols in it, ticketid and groupid
typical data would be
groupid ticketid
20 56
20 87
20 ...
11
votes
2answers
813 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?
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 ...
3
votes
3answers
315 views
Is a surrogate key better than a natural key in this case
I copied this code from here:
CREATE TABLE records(
email TEXT REFERENCES users(email),
lat DECIMAL,
lon DECIMAL,
depth TEXT,
upload_date TIMESTAMP,
comment TEXT,
PRIMARY ...
0
votes
1answer
111 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 ...
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 ...
3
votes
1answer
76 views
Optimised Schema for City-to-City Distance Table
This is my first question, forgive me if this question is simple. I'm stuck here, trying to implement the relations for the data represented below. I want to scale it vertically (new rows), instead of ...
1
vote
1answer
104 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 ...
12
votes
5answers
380 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 ...
3
votes
2answers
114 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, ...
0
votes
1answer
170 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 ...
1
vote
1answer
66 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
160 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 ...
4
votes
2answers
131 views
What happens to the index of a primary key after a DROP CONSTRAINT?
I am running PostgreSQL 9.1.4.
I have a table with many existing rows, and a bunch of other tables with foreign keys pointing to it, for which I am trying to :
1 - Remove the pkey constraint on the ...
0
votes
1answer
153 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 ...
6
votes
2answers
290 views
Should a log table get an id field or primary key?
I have a log table that captures the datetime stamp of when certain files were exported to another system.
The exportedLog table currently has three fields:
id (primary key)
messageId ...
1
vote
1answer
428 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 ...
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 ...
1
vote
1answer
105 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 ...
