Tagged Questions

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?
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 ...
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, ...
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 ...
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
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
157 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
3answers
316 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
112 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 ...
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
172 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 ...
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 ...
6
votes
2answers
291 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 ...
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 ...
3
votes
1answer
287 views

Surrogate key / Primary key: Better to use an existing unique data field or create a key field?

I am not sure if this question has been asked or not. At least I couldn't find it. I am curious about a primary key in terms of efficiency with data searching and retrieval. This is a hypothetical ...
3
votes
1answer
192 views

Using indexes to create multiple relationships to a table in order to enforce data integrity and add meaning

I'm working on an inventory database that uses supertype/subtype model. My question regards creating multiple relationships with the same tables using the primary key and composite indexes that also ...
4
votes
4answers
760 views

Primary key should be chosen so as attributes never change?

I was reading about keys in this DatabaseSystems book - The primary key should be chosen such that its attributes are never, or very rarely, changed. For instance, the address field of a person ...
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. ...
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 ...
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
453 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 ...
1
vote
2answers
254 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 ...
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 ...
7
votes
4answers
245 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 ...
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 ...
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) ...
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, ...