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

3
votes
5answers
221 views

Using Timestamp plus Integer as Primary Key

In Sql Server, what would the drawbacks and disadvantages be of using a primary key that is: Two Character Table Identifier + YEAR + MONTH + DAY + HOUR + MINUTE + MILLISECOND + random integer between ...
1
vote
1answer
85 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
30 views

Sybase ASA: Saving and dropping all foreign keys and most Primary Keys for (Bulk) Insert

Background Information: I am trying to migrate from an ASA 12 Database, to a ASA 12 Database by using a Remote Server. I have to migrate a lot of Rows (not all), spread over a lot of tables (not ...
1
vote
2answers
115 views

Column suitable for natural key?

I have read many articles now about natural vs surrogate primary keys, and came to the conclusion there is no single best practice. I have a table that will have around 2000 definite unique values, ...
0
votes
1answer
33 views

What is the difference between Primary key and Super key in DBMS

I am new to DBMS and I am still learning the theory. I am getting really confused with this key business and after googling I have narrowed it down to just 2 keys I dont get (primary and super key) ...
0
votes
1answer
33 views

Batch converting indexes to keys [closed]

I have a legacy MS SQL Server database where all the primary and foreign keys were created as indexes. I am developing the new version of our software with Entity Frameworks, which I have learned ...
0
votes
1answer
21 views

Database relationship refresher

All, After several years of programming applications, I'm looking to refresh my understanding of databases. What I knew to begin with was fairly basic, and honestly, it's disappeared over time. I ...
0
votes
2answers
29 views

primary_key problem in MySql view

I have 3 tables. and I want to create a view. There is no problem about generating SQL qeury and creating a view. but when I want to use this view in my web app it says me (LIKE THIS): "your view's ...
2
votes
2answers
46 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 ...
1
vote
1answer
84 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 ...
0
votes
1answer
98 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 ...
1
vote
2answers
53 views

Relationship with a table without unique/primary key

I have a database with 3 tables. Table Work, with a field named workName. Table SubWorks, which have a field subWorkName and work with a relationship with Work.workName. And table Activity with ...
2
votes
1answer
1k 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 ...
1
vote
0answers
73 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 ...
0
votes
1answer
33 views

Msg 2627 Violation of PRIMARY KEY constraint

trying to make a database for teams in a tv show here. but when I try and insert data into tblShowteam the following error made its appearance. Msg 2627, Level 14, State 1, Line 2 Violation of ...
0
votes
0answers
21 views

Should a surrogate key reflect one of my columns? [duplicate]

I currently have a column called LoadID which is a surrogate key that just increment by max()+1. I also have LoadNumber which always seems to have the exact same value of LoadID, since they will ...
1
vote
1answer
40 views

Almost sequential GUIDs vs random GUIDs?

I'm looking at using GUIDs (UUID) as primary key with clustered index in SQL Server. In my webb-app I have many clients who will create items client side in javascript and save them only now and ...
1
vote
2answers
54 views

compound primary key with separate id field for reference

I am trying to build locations into a database like so: Country -> Region -> Town Countries will be populated with a list of pre-defined worldwide countries, regions & towns are input by users. ...
0
votes
2answers
80 views

What is the advantage of using numeric primary keys in a relational database?

Many tables in a relational database system (in my admittedly limited experience) have this sort of structure (Oracle syntax as that's what I'm used to): create table widgets ( widget_id number ...
1
vote
2answers
376 views

How are auto_increment keys handled in INSERT (SELECT * FROM…)

I have table1 and table2 in MySQL. Both have a primary auto_increment key id. If the table schemas match and I do INSERT INTO table1 (SELECT * FROM table2) what happens with regards to the new rows ...
5
votes
3answers
3k views

How can you tell if a table in DB2 has a Primary Key?

We are using a tool that requires specific tables in our DB2 database to have a Primary Key defined. Is there a way using a select statement on the DB to see if a given table has one? Thanks.
3
votes
2answers
2k 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 = ...
1
vote
0answers
79 views

Crow Feet notation and identifying -vs- non-identifying relationships

Is it possible to distinguish between identifying and non-identifying relationships using the Crow Feet notation? Case in point: In table Book, part of the book's Primary Key is a FK to the Author ...
1
vote
1answer
139 views

Multiple primary keys in PostgreSQL

I have the following table: CREATE TABLE word( word CHARACTER VARYING NOT NULL, id BIGINT NOT NULL, repeat INTEGER NOT NULL ); ALTER TABLE public.word OWNER TO postgres; ALTER TABLE ONLY word ADD ...
0
votes
0answers
30 views

Difference between OID and Primary key

Although I understand basic difference that OID is external to the tuple and generated by the system and Primary key is inside the tuple and generated by the db designer, I want to understand why OID ...
0
votes
2answers
2k 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
107 views

Listing indexes and constraints

I am looking at a SQL Server database for an application I have inherited. I have not looked into SQL Server for approximately 10 years, so please bear with me. The database table I am looking at has ...
1
vote
1answer
106 views

What's better here - a single column or multicolumn primary key?

Let's assume you have a table groups and a table item. Each item belongs to exactly one group. It is an inherent part of that group. An item cannot exist outside of a group and it cannot be moved to ...
0
votes
1answer
137 views

postgresql bigserial or sequence using jpa

I'm a java developer working with jpa, before to define the indexes in my entities i would like to known which one is faster o better to use?.
1
vote
1answer
103 views

How do I validate all my constraints in postgresql?

It seems like I have been bitten by the first bug described in the release notes of postgresql 9.3.4: http://www.postgresql.org/docs/devel/static/release-9-3-4.html I now have e.g. duplicate primary ...
2
votes
2answers
41 views

EAV-like table with both primary column pointing to the same foreign column

Background: I'm a programmer who, while had to work with simple databases several times, never had to work with complex ones. I.e. I'm proficient with basic stuff, but clueless about advanced features ...
1
vote
2answers
839 views

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

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, ...
0
votes
0answers
107 views

Converting UUID's from CHAR(32) to BIN(16)

I have a few questions relating to UUID's, but before I go on, let me explain our schema a bit, first, we're on MySQL 5.5 (Percona's version to be exact)... In each applicable table, we use a two ...
5
votes
1answer
225 views

Why does InnoDB table by default uses the UNIQUE constraint instead of the PRIMARY KEY?

In my MySQL database I have a table with 4 columns, the PRIMRAY KEY, two columns with a FOREIGN KEY CONSTRAINT and one UNIQUE index on all fields except for the PRIMARY KEY. The SHOW CREATE TABLE: ...
0
votes
0answers
30 views

Primary keys optimized for database sync

I am implementing lazy multi-master database synchronization in star shape network using SymmetricDS. I can not estimate the number of nodes, so I can't use simple auto-increment integers. Database ...
1
vote
1answer
47 views

one to one relationship

This is my database structure: Users {user_id, username} Profiles {profile_id, user_id, user_views} My question is: If I do it the way mentioned above, should I to set user_id as primary key or ...
2
votes
2answers
546 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
1answer
48 views

Can a complex primary key made up of 4 fields have only one of this fields set to AUTO INCREMENT?

I have a complex primary key, I wonder if what I write in the title is possible: I have a look up table with 4 fields (they should compose the primary key of the table): 1) an INT column; 2) ...
4
votes
3answers
174 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
3answers
69 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
2answers
98 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 ...
0
votes
0answers
21 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 ...
1
vote
1answer
103 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
59 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
81 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. ...
1
vote
1answer
781 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
570 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 ...
2
votes
2answers
114 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
0answers
30 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 ...
7
votes
1answer
4k 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 ...