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
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
153 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
266 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
771 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
603 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
1k 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
404 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
707 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
488 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
77 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
91 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
295 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
4k 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
801 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
704 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
506 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
2answers
833 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
13k 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
171 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
87 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
567 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
355 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
141 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
647 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
1answer
243 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
165 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
88 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
882 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
205 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
155 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
57 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

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
57 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
168 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
210 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
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
1answer
296 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
136 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
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
643 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
301 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
57 views

Two tables with the same primary keys

Can two tables have the same primary keys? For instance:
2
votes
1answer
37 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
141 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 ?