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
2answers
9k 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 ...
3
votes
2answers
4k views

Postgres primary key sorted in the reverse order. Will it use the index efficiently?

My Rails database is backed by Postgres database. As you may know, each table in Rails gets assigned a primary key which is of Integer type and is indexed. My list views show records in the reverse ...
3
votes
1answer
194 views

Update multiple Ids when SET = (Sql Query)

I want to update multiple records however the value being set is based on the primary key of the given record. This is not an issue with a single record as I can use: UPDATE T1 SET A1 = (SELECT ...
3
votes
3answers
273 views

Is Primary Key Non Clustered recommended?

I am designing a database table. Columns are RouteId ,Origin , Destination. RouteId is a string, it is always unique but length will be more than 60 characters. Since RouteId is unique, I am ...
3
votes
2answers
5k 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 = '...
3
votes
1answer
782 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, namely:...
3
votes
1answer
670 views

Drop primary key without dropping an index

Is it possible to drop primary key constraint on a table without dropping the index? I have a situation when I want to remove the PRIMARY KEY to get rid of the NOT NULL constraints and set it to ...
3
votes
1answer
2k 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 ...
3
votes
2answers
406 views

Is index_type ignored on MySQL primary keys in create table?

I have seen a create table whose primary key had an identifier where it should have an index_type, something like that: create table a (foo INTEGER, PRIMARY KEY foo_id (foo)); The thing is that ...
3
votes
3answers
762 views

MySQL - Unable to Convert Primary Key to Auto Increment

I have a MySQL table where the Primary Key (int) was set by the software to be equal to the Primary Key (auto incremented) of the main table. Because of a change in design I now need the field to auto ...
3
votes
1answer
530 views

How to efficiently create a new PRIMARY KEY CLUSTERED definition online

Given a table with a PRIMARY KEY definition (acting as the clustered index), I want to efficiently change the definition of the PRIMARY KEY (or at a minimum, just the clustered index) online. I would ...
3
votes
0answers
46 views

How to manage primary key value while multiple secondary server sync with primary server?

we have three secondary database server in different cities and one primary server. my question is i want asynchronous transaction commit to all three database but primary key data conflict occur ...
3
votes
0answers
83 views

Clustered index on a join table

Currently I have a table defined as follows, but I'm not sure what to cluster the table on: CREATE TABLE [Security].[UserGroups] ( [UserId] INT NOT NULL, [GroupId] INT NOT NULL, [IsAdmin] ...
3
votes
0answers
92 views

Superkeys of this relation

I am trying to find the superkeys of this relation, but I am having troubles finding out how many superkeys there are and exactly what they are. I figured out that the candidate keys were {A},{B},{C},{...
2
votes
3answers
299 views

Why does PK constraint require a separate index when I already have a suitable unique clustered index?

This is part of table definition from here: CREATE TABLE [dbo].[JobItems] ( [ItemId] UNIQUEIDENTIFIER NOT NULL, -- lots of other columns CONSTRAINT [PrimaryKey_GUID_HERE] PRIMARY KEY ...
2
votes
4answers
5k views

NVARCHAR column as PRIMARY KEY or as UNIQUE column

I'm developing a SQL Server 2012 database and I have a doubt about nvarchar columns as primary keys. I have this table: CREATE TABLE [dbo].[CODES] ( [ID_CODE] [bigint] IDENTITY(1,1) NOT NULL, ...
2
votes
3answers
836 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 ...
2
votes
2answers
707 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 ...
2
votes
2answers
576 views

Can different tables have columns with the same name if those columns are not primary keys? [closed]

I know it's a simple enough question but my textbook is very confusing about it.
2
votes
5answers
129 views

How can I allow part of a key to be null and enforce uniqueness of records with nulls?

I have an Access table called Engagement with four fields: Emp_id, Year, Week, Act_id It records when an employee is/was engaged in an activity. Each field is Number and each makes up part of the ...
2
votes
2answers
863 views

Primary key auto increment best practices

Apologies if something similar has already been asked, I wasn't sure of the best term to search for to get what I was after, so please let me know if there is already a bunch of other related ...
2
votes
2answers
14k 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] ...
2
votes
1answer
172 views

Is it require to fill all tables inside of the database?

Now,I created upper that table and in order to test I will give 3 textboxes values as below. SqlCommand cmd = new SqlCommand ("Insert Into dbo.PrnInf(PrnName, PrnSurName, PrnEgn ) Values ('"+...
2
votes
1answer
1k views

size of a primary key and foreign key

Do a primary key and a foreign key referencing it must have the same column size in sql server 2008 ? could someone indicate me a link to a documentation explaining this ? Thank you for help.
2
votes
1answer
3k 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?.
2
votes
3answers
88 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 ...
2
votes
2answers
7k views

Update datatype of primary key without deleting the table-SQL Server [closed]

I have created a table State which my other table, Addresses references. CREATE TABLE State ( StateId varchar(20) PRIMARY KEY NOT NULL, Text varchar(40) NOT NULL ) I want to change its ...
2
votes
2answers
582 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 ...
2
votes
1answer
365 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 ...
2
votes
2answers
143 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 ...
2
votes
4answers
664 views

Why would i get a duplicate write error of auto-generated primary key?

Hi I'm not a DBA so forgive my ignorance. I don't have access to administrator tools etc. However, I do know that we have a live server which is approaching capacity ie 80%-90% full perhaps. What is ...
2
votes
2answers
53 views

Copy primary keys/clustered indexes from Prod to DEv/Test env

We followed the best practices of creating Primary Keys/Clustered indexes on tables which are actively queried on prod. Now there is a scenario where we need to get the similar above changes done ...
2
votes
1answer
247 views

Can I use a Clob as a table key (from Java)?

Following a suggestion made to another question to store large amounts of text: can one use a CLOB as primary key field (i.e., from Java's DataNucleus implementation of JDO)? I know it is probably a ...
2
votes
2answers
3k views

Single Identity column and composite key, which to make primary?

I've got a table that's a working copy subset of a base table. The base table has a composite key with a clustered index. However I need a single column unique identifier for my framework to work ...
2
votes
2answers
175 views

Does a composite primary key break the first normal form?

Does having a composite/compound key (using multiple columns/attributes) to form the primary key break first normal form? (I am assuming that this is handled by the DBMS, and so the attribute ...
2
votes
2answers
100 views

Does my table need a primary key and clustered index change?

I have a table holding 1.7 Milion rows. Definition: CREATE TABLE T( [ID] [uniqueidentifier] NOT NULL, [AID] [uniqueidentifier] NOT NULL, [BID] [uniqueidentifier] NOT NULL, [iType] [int] NOT NULL, [...
2
votes
2answers
1k views

DynamoDB - Multiple range keys

Is it possible to have multiple fields as range key? Say I have a table where each row is uniquely identified by <A,B,C> ------------------------------- A | B | C | D | E | ----...
2
votes
1answer
207 views

Why does MySQL increment the auto_increment value when there's a constraint violation?

I have a table in a MySQL database with one column id, which has auto_increment and the Primary Key. There's also a Unique constraint in this table, on different columns. I noticed that when I try to ...
2
votes
1answer
156 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 ...
2
votes
2answers
59 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 ...
2
votes
1answer
1k 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
1answer
1k views

Retrieving all PK and FK

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?
2
votes
1answer
1k 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 ...
2
votes
3answers
2k views

performance of multi field primary key or 'contrived' 'semi artificial' key

This is not a question regarding the benefits or otherwise of using an artificial auto increment key in any given table against using a multi-field 'primary key'. That discussion (or argument) can ...
2
votes
1answer
64 views

Surrogate vs composite key in hierarchical data structure

I'm currently evaluating a schema for a hierarchical data structure. My main problem is how I should design the schema to prevent inconsistent data (reference of foreign key in another hierarchy). The ...
2
votes
1answer
180 views

Oracle: How to create a “reverse” index primary key?

I can create a primary key for a table and then reverse it with the command alter index T_PK rebuild reverse online; How can I create it reversed?
2
votes
1answer
211 views

Foreign Keys to tables where primary key is not clustered index

I'm focusing on SQL Server here, but I think this is a database agnostic question, really. I have a table whereby a Guid is logically the primary key. However, uniqueidentifiers apparently make ...
2
votes
1answer
302 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 then....
2
votes
2answers
137 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 ...
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, ...