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

4
votes
3answers
197 views

Is matriculation number a good primary key?

I am trying to design a grade assignment database and i am facing the following dilemma. I have a student table which has the following characteristics: matriculation number which i know for a fact ...
2
votes
2answers
1k views

Best practice to avoid a composite key?

Suppose my students fill every year a survey, containing some questions (called fields) which answer is real-valued (called value). Every student may answer the survey, at most once a year. A simple ...
0
votes
0answers
22 views

Impact of additional int field as primary key

Directly to the point, at one time I have a table like this: create table item( item_id varchar(50), constraint pk_item1 primary key (item_id) } However from my discussion one day, there is ...
0
votes
2answers
114 views

Primary key for link/junction table with time-dependent data

I want to link two tables in such a way that for any given time period the relationship is one-to-one, but over time the relationship can change. As an minimal example, suppose I want to list the ...
1
vote
1answer
172 views

MySQL, temporary on disk table on primary key retrieval?

MySQL 5.5.24, Windows 7 64-bit. I've InnoDB table with primary key defined on three columns: CREATE TABLE `common_descriptions` ( `object_id` mediumint(8) unsigned NOT NULL DEFAULT '0', ...
0
votes
1answer
78 views

Table design for departments with duplicate department numbers

How should I define a database table for my company's departments where we have a set of duplicate department numbers used by an upper management tier. dept_id dept_name manager_id ...
0
votes
1answer
138 views

How to set up multiple fields as primary key in MySQL?

I have a table with fields EmployeeID blahblah blahblah2 ..... RecordMonth RecodrdYear so each employee should only have a matching entry for a month, year, Emp#. How do I set up a table. ...
2
votes
2answers
196 views

Should I replace my varchar primary key with an integer primary key?

I have a table that has a varchar primary key that's associated with "Base Product Codes". They rarely change, and if they do, it's usually just being deleted or added. I figured it would be better ...
1
vote
1answer
2k views

MySQL Subquery returns more than one row

I have a table namely tbl_plays. It has the following columns and data id(PK)| gameid | drawid | etc ---------------------------------------------- 1 | 1509 | 73 | ...
1
vote
1answer
1k 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
0answers
31 views

Keys of a relation

I got relation: R = { (ABCDEF) , (A B F -> C, B C -> F, F -> A, F -> B, D E -> E, E -> D)} keys of this relation are: {BCE}, {EF} But i dont get it... why? Why not only {EF}.. it got less ...
0
votes
0answers
69 views

Overcoming the HDD IOPS limits on a billion row table? Is getting SSD the only solution?

I have a table with this structure: CREATE TABLE `requests` ( `userid` int(11) NOT NULL, `messageid` int(11) NOT NULL, `queueid` int(11) NOT NULL AUTO_INCREMENT, `problem_id` int(3) NOT NULL, ...
2
votes
1answer
91 views

Primary Key Question

Building a database on Access 2007 and having trouble selecting the primary key. The tables consists of a student table and another table where the students are checked in. I would have liked to make ...
0
votes
1answer
119 views

primary key: username+current time or serial

I need to choose a primary key, but I'm not sure what the best option would be. Currently I'm in a scenario where a user has machines (workers) calculating hashes. For each user I track the current ...
0
votes
0answers
80 views

How to retrieve pkey value in mysql/python on duplicate key update

I'm currently using _mysql library in python. it uses pkey(int, auto increment) value that's created when inserting data using function conn.insert_id() This is how the query is made. INSERT INTO ...
1
vote
1answer
110 views

Can one part of a multi-column primary key be used across tables?

I want to use SQLite as file format for my app, a reporting program. Below is an idea of the kind of schema I'll use (just the first few tables). There will be one report per file and all tables are ...
1
vote
1answer
99 views

Proper implementation of child (derived) table PK/FK during single and multiple inheritance hard coded generalization physical data model

Below is an image of hard coded generalization UML logical model of entities. I'd like to implement these classes as tables in a physical data model. Two derived classes/entities (Class2 and ...
1
vote
3answers
337 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 ...
2
votes
1answer
966 views

Need for indexes on foreign keys

I'm struggling with indexes, primary keys and foreign keys... And the need of having them all. If I have two tables, both of them have an integer as a primary key. The first table references ...
2
votes
3answers
72 views

Platform independent select ordered by primary key

Is there a reasonably standard way to perform a select on a single table and have the result set ordered by primary key without having to specify the primary key condition explicitly? Ideally I wish ...
0
votes
1answer
125 views

Implementing foreign keys on generalized data model

Suppose the ASCII tree below is a UML generalized concept model. To represent these entities/classes in UML, lets assume [mammal] a super class, and the level 1 classes, [wild] and [domestic] are sub ...
5
votes
3answers
248 views

How do I find all tables in a DB that have no explicit primary key?

A Google search spewed forth millions of hits on how to find tables without clustered indexed, the PK normally being the clustered index of a table. However, a table could easily have a natural key as ...
1
vote
1answer
190 views

MySQL auto_increment delivers different primary key in MyISAM and InnoDB

I have a table with auto incremental primary key. If I want to change a row and add new rows I got different primary keys depending on engine MyISAM and InnoDB. The original table is: pk.. ...
0
votes
3answers
530 views

Performance of primary key IDs with gaps (but in sequential order)

I know that having non-sequential IDs is bad for index performance. But assuming all my IDs are created in correct order, but with large gaps: i.e: 154300000 283700000 351300000 464200000 ...will ...
0
votes
2answers
4k views

MySQL - Autoincrement does not increment sequentally if last row was deleted

I have a table which contains an auto incremented primary key id. If I delete the last row (highest id, for exampe id = 6) and insert a new row, the new id starts at 7. Which paramater I have to ...
2
votes
2answers
38 views

best approach to keys, if data from separate DBs to be presented in same UI

Suppose we are creating databases containing information about monkeys, and a user interface to present monkey information to users. We have two separate databases. The schemas are identical (and are ...
4
votes
2answers
2k views

Composite Primary Key efficiency as a Foreign Key

I have a table with a Composite Primary key (consisting of 4 columns) which is used to ensure no duplicates are entered into the table. I am now in need of a new table which will need to reference the ...
0
votes
1answer
71 views

How to arrange rows selected in MYSQL query?

I'm fetching data from a table "TABLE_A", it's having Primary Key as "PK_ONE" (which is unsigned INT) . I'm fetching data from "TABLE_A" with where clause on PK_ONE ; Ex: SELECT PK_ONE from TABLE_A ...
6
votes
5answers
924 views

Sharing a single primary key sequence across a database?

Is it an acceptable practice to use a single sequence as a primary key across all tables (instead of a primary key being unique for a given table, it is unique for all tables)? If so, is it ...
1
vote
1answer
168 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 | ...
16
votes
2answers
2k views

Does the order of columns in a PK index matter?

I have a few very large tables with the same basic strucure. Each one has a RowNumber (bigint) and DataDate (date) column. Data is loaded using SQLBulkImport every night, and no "new" data is ever ...
0
votes
1answer
115 views

Use surrogate or natural PK if a row is uniquely identified by two or more columns?

I am having a hard time choosing between natural and surrogate PK for my database table. The database is for a ranking system for an MOBA game. Here is more or less what I would do if I were to go ...
1
vote
2answers
112 views

Relations, dependencies and normal form

I have some issues in understanding about relations and dependencies. I will show two examples and what I want to know: Assume the relation GTX {G,T,X} with the following dependencies: G,X -> T ...
7
votes
1answer
5k views

Autoincrement primary key in CREATE TABLE … AS SELECT

I created table using a complicated select query via CREATE TABLE ... AS SELECT.... How can I add an autoincrement primary key in this query? For example: create table `user_mv` select ...
1
vote
2answers
1k 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 ...
3
votes
2answers
602 views

Why does Postgres generate an already used PK value?

I'm using Django, and every once in a while I get this error: IntegrityError: duplicate key value violates unique constraint "myapp_mymodel_pkey" DETAIL: Key (id)=(1) already exists. My database ...
1
vote
1answer
76 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 ...
2
votes
2answers
970 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
1k 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 ...
1
vote
1answer
153 views

Is there a way to extract a report of or script all of the primary keys in a database?

I have just discovered that deployed copies of one of our databases have no primary keys. This is probably the fault of the SSMS Import/Export wizard, which has done the same to me. Our master copy of ...
0
votes
1answer
305 views

Postgresql table with one integer column, sorted index, with duplicate primary key

I want to have a table in PostgreSQL with these properties: One column with an integer Allow duplicates in this column. Duplicates will be rare. Duplicates have no meaning logically and will be ...
2
votes
1answer
31 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
1answer
369 views

Does the size of the primary key contribute to table size?

I have a table (InnoDB) with data length around 36G and index length 23G. It had a composite primary key across three columns. As an initial attempt to reduce the size of this table, I dropped the ...
2
votes
2answers
2k views

IDENTITY_INSERT is set to OFF?

Here is my script of my db , CREATE TABLE [dbo].[MyTable]( [ID] [int] IDENTITY(1,1) NOT NULL, [Code] [nvarchar](25) NULL, [Name] [nvarchar](25) NULL, [dob] [date] NULL , CONSTRAINT [PK_MyTable] ...
0
votes
1answer
148 views

Relational database primary key requirements

I am trying to put together an ER Diagram to design a fairly simple asset tag tracking/inventory management system for our servers/switches/UPS/etc. The top-level entity corresponds to the business ...
0
votes
2answers
902 views

Use timestamp(or datetime) as part of primary key (or part of clustered index)

I use following query frequently: SELECT * FROM table WHERE Timestamp > [SomeTime] AND Timestamp < [SomeOtherTime] and publish = 1 and type = 2 order by Timestamp I would like to optimize ...
0
votes
1answer
877 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
308 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, ...
3
votes
2answers
467 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
1answer
1k 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?