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.

learn more… | top users | synonyms

2
votes
1answer
665 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, ...
2
votes
1answer
307 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
58 views

Two tables with the same primary keys

Can two tables have the same primary keys? For instance:
2
votes
1answer
39 views

Should I create many ID for a same Person/entity in my DB?

In my model, I must store the name of the teacher of some kids and the classroom in which the teachers work, let’s say. All of the hundreds of teachers in the schools have 1 and only 1 classroom, ...
2
votes
1answer
142 views

If A and B are candidate keys, then AB is candidate key or not ?

If R{abc} is a relation, f{ a->b b->a a->c b->c }, then a and b are candidate keys. What about ab ? Is it a candidate key or is it a super key or both ?
2
votes
2answers
95 views

Is there a good way to select a row where the key represents the lower bound of a range?

I am designing some tables and a number of the tables have as the key a lower and upper bound which never overlap. I want to query by a value that can fall withing any of those ranges. If I keep both ...
2
votes
2answers
2k views

Two primary keys to one foreign key

racing_couple ID (PRIMARY) breeding_couple ID (PRIMARY) egg IDpar(FOREIGN KEY) I have 2 IDs from 2 different tables (racing_couple and breeding_couple) and they are primary keys. In 3. table (egg) ...
2
votes
1answer
2k views

prevent MySQL Workbench to add index for foreignkey constraint when it is first part of primary

I know for each foreign key we should make an index on its column, But it is not necessary when that column in first part of PRIMARY index. MySQL accepts this query: CREATE TABLE IF NOT EXISTS `...
2
votes
1answer
44 views

NEWB Getting to KNow New Database and Understand Where Relationships Are

I'm teaching myself SQL using MYSQL and a textbook. While working through the textbook I'm using a database I'm kinda familiar with - an exported copy of my CMS driven websites database. My website ...
2
votes
2answers
189 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
5k 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
1k 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 varbinary(8)....
2
votes
0answers
29 views

How should I select a primary key in my use case

I have an app wherein I want to give offers to certain devices which fall in range of IMEI numbers (I have a separate algo to calculate this range). So I want to create a structure in which I have a ...
2
votes
1answer
71 views

Add a primary key constraint to an existing unique column

I've seen lots of information around adding a new primary key column to a large database table, but I have a somewhat different situation. I have a large table (~90,000,000 records), that has no ...
2
votes
1answer
68 views

Primary key addition for a table which contatins no data is too slow

We are using mysql. We are running 10 alter queries which contains 3 Primary key deletion and 4 column addition and 3 primary key addition. And this alter query is on three tables which don't contain ...
2
votes
1answer
11k views

Linking Fields From 2 Different Tables (Microsoft Access)

I am trying to link 2 fields from 2 different tables on Access but I will give an example to try and make the issue clearer. I have 1 table called "Employee Details" which contains the field '...
2
votes
0answers
168 views

Storing URLs in a database

I am thinking about storing URL's in my database, but I am not sure whether the way I use is an efficient way to do things or not. Table name : url url_id (Primary Key & Auto Increment) url_value ...
2
votes
2answers
9k views

Composite primary key from multiple tables / multiple foreign keys

I want to build a composite primary key made up of foreign keys from two different tables. I'm still pretty green to this... A great answer was given to building a composite key from multiple foreign ...
2
votes
0answers
2k views

“Temporary file write failure” when trying to change primary key in MySQL

In MySQL, When I trying to change primary key by this query: ALTER TABLE `resources` DROP PRIMARY KEY, ADD PRIMARY KEY (`role_id`, `category_id`, `lang`, `model`, `rule`); I execute this query ...
2
votes
1answer
995 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
617 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
513 views

Why are runtimes different for a table including a primary key versus a table with a clustered unique index added after population [closed]

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 ...
1
vote
2answers
7k views

Is it recommended to use Identity as an alternative to primary key?

We can declare an Identity like id_num so that id_num will have an increment of unique numbers. CREATE TABLE new_employees ( id_num int IDENTITY(1,1), fname varchar (20), minit char(1), lname ...
1
vote
2answers
169 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
3k views

VARCHAR primary key - MySQL

Currently, I have a categories table with 2 columns - category VARCHAR(50) NOT NULL PRIMARY KEY and parent VARCHAR(50). The parent column is a foreign key (FK) to the category column. This seems like ...
1
vote
3answers
10k views

What could cause an auto increment primary key to skip numbers? [duplicate]

I have a table created with the following T-SQL: CREATE TABLE [dbo].[TableName]( [id] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, ...
1
vote
3answers
1k views

Normalization: 'zip code' as a primary key

I've seen frequently that you should not have all address fields (zip, street, city, state, country, etc) in the same table as your user info table (pk_id, name, email... ). This would be in ...
1
vote
3answers
1k views

does the mere existence of a primary key increase query speed for large data sets?

So, I have a table with about 2.4M rows and ~30 columns. There is no unique column within the data. The data is transaction logs from stores where each transaction number is only unique on a per store ...
1
vote
2answers
272 views

What and How is the ID antipattern?

What is the ID antipattern and why / under which circumstances is it considered an antipattern? The id antipattern is having a unique ID column in each table. The ID is generated for each new ...
1
vote
1answer
83 views

Automatic index creation for primary vs. foreign keys in Postgresql

For PostgreSQL 9.4 (http://www.postgresql.org/docs/9.4/static/ddl-constraints.html) it is stated that A foreign key must reference columns that either are a primary key or form a unique ...
1
vote
1answer
116 views

Is it ok to create additional column for helping other column to be a PK?

Let's say a Person can have more than one nickname. He(she) must enter his(her) nicknames alongside with other details: Here is the table columns: User_Id, Nickame, Age Of course, for now there ...
1
vote
1answer
127 views

Shorthand for on primary key?

Is there a shorthand for merging rows based on the primary key? For example: MERGE INTO [MyDatabase].[MySchema].[MyTable] AS target USING [MyLinkedServer].[MyDatabase].[MySchema].[MyTable] AS source ...
1
vote
1answer
83 views

When do you add your foreign keys?

I'm scripting out a fairly simple database so I can simply hand off a script to someone else for an application to use. I'm curious about where best practice/the community stand on this: When should ...
1
vote
1answer
2k views

Changing primary key from varchar to int

I have a user table where I am use varchar as primary key. Now from a different place I got the performance tip that int is better than varchar for primary key. So I would like to change my primary ...
1
vote
1answer
229 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
3answers
95 views

Is it a good idea to use a personal number as a primary key for a customer table?

We are designing a new system to handle customers at our firm and me and another developer are split on wether we should use the customers personal numbers as a primary key. My preference would be to ...
1
vote
2answers
209 views

DB2 10.5 - When inserting, the database is trying to reuse index keys on GENERATE ALWAYS

I am trying to insert a row into a database table which has a GENERATED ALWAYS primary key column. The table has over 1000 rows, and the primary keys start at 1. When I try to do an insert, the error ...
1
vote
1answer
43 views

Need advice on Primary and Clustering Keys of a table

I need advise on a table which is part of a development project I'm involved. A brief summary on the use of the table: Data from multiple databases will be consolidated in this table for the purpose ...
1
vote
3answers
651 views

Copy table with separate sequences for serial columns

While creating a column management tool I came across the need to quickly replicate tables in PostgreSQL so I would not test new tools with non-test tables. To effectively test the new column tool ...
1
vote
2answers
101 views

What Columns to create Indexes on?

I have a table , which contains upto 20 columns. The data is selected, inserted, and deleted by 3 columns: Username, StartDate, and EndDate. These 3 columns are NOT unique. The table is a ...
1
vote
1answer
351 views

How to include in the result of a `SELECT … GROUP BY …` all the other columns that are functionally dependent on the grouping ones?

I'll base this question on a toy example. Let this be table A: A ------------------------- U | V | W | X | Y | Z ------------------------- a | b | c | 1 | 6 | 8.3 a | b | c | 1 | 4 | ...
1
vote
2answers
232 views

Composite Primary Key column order

How would you do the ordering of the PK columns in this case (Oracle 11gR2 Standard Edition): I have a database where I offer the same application to many users. I want to be perfectly sure that all ...
1
vote
1answer
3k views

Auto Increment Primary ID inserting random values

Today I was asked to look into this problem and I have never seen anything like this before. A table in our MySQL database, with a primary IDs column, which is set to autoincrement int, had some weird ...
1
vote
1answer
447 views

MySQL looking up more rows than needed (indexing issue)

In our MySQL 5.5 database, we have following InnoDB table with 30M+ rows: +----------------+-------------+-----------------+--------+ | participant_id | question_id | given_answer_id | status | +-----...
1
vote
1answer
3k 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
1k 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
74 views

Primary Index VS Unique Index Performance

I have the following table: CREATE TABLE `actions` ( `user_id_from` int(11) unsigned NOT NULL, `user_id_to` int(11) unsigned NOT NULL, `action_type` int(11) NOT NULL, `counter` smallint(11) ...
1
vote
1answer
62 views

Converting a composite primary key to composite unique, and adding an auto-incremented PK column

Background I have taken over responsibility for a table that, in the long run, will grow monolithic. The table is a very simple link table with 2 columns, call them col1 and col2, both being foreign ...
1
vote
1answer
46 views

Chance of duplicate on single select max into statement

Against my will, I am told to use the following method to generate a new pk value. INSERT INTO SERVER_MONITERING(SM_SEQ, CPU_USE, MEMORY_BASE, MEMORY_USE, DISK_BASE, DISK_USE, SERVER_IP,...
1
vote
1answer
593 views

Rename the constraint of a possibly referenced primary key in PostgreSQL

Is it safe to use this command just like that, even though the table might be referenced from other tables as foreign key? ALTER INDEX foo_pkey RENAME TO pk__foo__id; I have just tried it and it ...