primary-key

Django queries - id vs pk

Hi all, When writing django queries one can use both id/pk as query parameters. Object.objects.get(id=1) Object.objects.get(pk=1) I know that pk stands for Primary Key and is just a shortcut, according to django's documentation. However it is not clear when one should be using id or pk. Thanks! ...

Getting ORA-00001(unique constraint violated) when COMMITing?

We're getting a ORA-00001 (unique constraint violated) in a batch job. However, the error occurs when a COMMIT is issued, not at the time the offending record is inserted. Questions: How come that the unique constraint is checked at COMMIT? (Are there some settings we can use so that the check occurs at the time of the INSERT?) How ca...

Is there an index on the primary key of a table?

Does having a primary key column mean there is an index on that column? If so, what kind of index is it? ...

Why use primary keys?

What are primary keys used aside from identifying a unique column in a table? Couldn't this be done by simply using an autoincrement constraint on a column? I understand that PK and FK are used to relate different tables, but can't this be done by just using join? Basically what is the database doing to improve performance when joini...

Should I also index columns included in a PRIMARY KEY?

This question suddenly popped into my head... I have a table that ties two other tables together based on their ID. The CREATE TABLE looks like this: CREATE TABLE `ticket_contact` ( `ticket_id` INT NOT NULL, `entity_id` INT NOT NULL, `notify` INT NOT NULL DEFAULT 0, PRIMARY KEY (`ticket_id`, `entity_id`...

Need to update the primary key on existing objects in GAE Java

Hi, I am building a web app using GAE Java. I have a class that uses a Long ID (generated by appengine) as its primary key. I now want to create a new class that would be the parent class to this original class (a one to many relationship) however the child needs to have a primary key of type "key", not the Long ID I have now. What...

SQL - many-to-many table primary key

This question comes up after reading a comment in this question: http://stackoverflow.com/questions/2190089/database-design/2190101 When you create a many-to-many table, should you create a composite primary key on the two foreign key columns, or create a auto-increment surrogate "ID" primary key, and just put indexes on your two FK co...

GraniteDS on GAE datastore JPA - Key class problem

I'm using GraniteDS(2.1.0RC2) on GAE with JPA annotiations. I have following class on flex side: [Bindable] [RemoteClass(alias="models.User")] public class User { public var key :String; public var login :String; } and on java side: @Entity public class User { @Id @GeneratedValue(strategy = GenerationType.IDENTITY) priv...

Primary key versus key

When creating a mysql dump containing the structure of my database, one of the tables shows the following: CREATE TABLE `completedTransactions` ( `paymentId` int(10) unsigned NOT NULL, `timestamp` int(15) unsigned NOT NULL, `actionTaken` varchar(25) NOT NULL, `response` varchar(255) NOT NULL, `responseCode` int(5) NOT NULL, ...

What are the pros and cons of using multi column primary keys?

I would like to see an example of: When this is appropriate When this is not appropriate Is there a time when the choice of database would make a difference to the above examples? ...

MySQL - Auto Increment after delete

I have a MySQL table with a primary key field that has AUTO_INCREMENT on. After reading other posts on here I've noticed people with the same problem and with varied answers. Some recommend not using this feature, others state it can't be 'fixed'. I have: course table - with 2 fields - courseID, courseName Example: Number of records i...

need help in primary key and foreign key

Hi all, I need help in auto populating the primary key values in foreign key table while inserting data in foreign key table. For Example: I have created table: create table Patient ( PatientId int IDENTITY(1,1) primary key, FirstName varchar(50), SurName varchar(50), Gender char(20), ) Say 5 rows are there in this Patient Table: Say...

Defining a particular strategy of ID generation in Grails

Hi, how i can define and use a strategy of ID (primary keys) generation for my domain classes ? I want to put a prefix for the primaryKey of some domain classes like customers and manufacturers based in the ID (primary key) of their hometown. I know that Hibenate has a API for this, but who i can integrate such strategy in my Grails app...

Add primary key to a table with many records

Hi, I have a table in SQL Server 2005 containing 10000054 records; these records are inserted through a bulk insert operation. The table does not contain a primary key and I want to have one. If I try to modify the table's structure, adding a new column, PK, set as int with isidentity, the management console gives me a warning: "Chan...

performance penalty of strings as primary keys?

Hi all, What would be the performance penalty of using strings as primary keys instead of bigints etc.? String comparison is much more expensive than integer comparison, but on the other hand I can imagine that internally a DBMS will compute hash keys to reduce the penalty. An application that I work on uses strings as primary keys in...

SQL Data Type for primary key in format of a version number

The table I am working on has a primary key which is essentially a version number. A couple of examples of the version number are below: 1.0.0.0 1.1.2.24 Varchar seemed the most obvious choice to me, but I'm looking to set the primary key as a clustered index - I have read on SQL performance sites that you should avoid using varchars ...

Should I use a unique string as a primary key or should I make it as a separate autoincrement INT?

Possible Duplicates: Surrogate Vs. Natural/Business Keys When not to use surrogate primary keys? So, I would prefer having an INT as a primary key with AI. But, here is the reason why I am considering making a unique string as a primary key: I don't have to query related tables to fetch the primary key since I will already k...

Meaning of Primary Key to Microsoft SQL Server 2008

What meaning does the concept of a primary key have to the database engine of SQL Server? I don't mean the clustered/nonclustered index created on the "ID" column, i mean the constraint object "primary key". Does it matter if it exists or not? Alternatives: alter table add primary key clustered alter table create clustered index Doe...

SQL Server: Non-null unique vs. Primary Key

Let's say I have some table T with some non-null field A. There is a clustered index on A. Consider the following two options: I make A the primary key. I create a UNIQUE constraint for A. Where's the difference? I know what a primary key is, and I know that there is a conceptual difference w.r.t. database theory. But what is the act...

How to choose the clustered index in SQL Server?

Usually the clustered index is created in SQL Server Management Studio by setting the primary key, however my recent question about PK <-> clustered index (http://stackoverflow.com/questions/2262998/meaning-of-primary-key-to-microsoft-sql-server-2008) has shown that it is not necessary to set PK and clustered index to be equal. So how s...