primary-key

Why is it not good to have a primary key on a join table?

I was watching a screencast where the author said it is not good to have a primary key on a join table but didn't explain why. The join table in the example had two columns defined in a Rails migration and the author added an index to each of the columns but no primary key. Why is it not good to have a primary key in this example? c...

MySQL Views in Navicat - How to define 'primary key'?

Often when I define a View in Navicat I receive the following message: xxx does not have a primary key. Updates to this table will be done using the following pseudo statement: UPDATE xxx SET ModifiedFieldsAndValues WHERE AllFieldsAndOldValues LIMIT 1 Obviously I only use my Views for viewing data, not updating. But this did make m...

Algorithm for unique CD-KEY generation with validation

Hi, I am trying to create a unique CD-KEY to put in our product's box, just like a normal CD-KEY found in standard software boxes that users use to register the product. However we are not selling software, we are selling DNA collection kit for criminal and medical purposes. Users will receive a saliva collection kit by mail with the ...

MySQL: Determine Table's Primary Key Dynamically

I'm, generating a SQL query like this in PHP: $sql = sprintf("UPDATE %s SET %s = %s WHERE %s = %s", ...); Since almost every part of this query is dynamic I need a way to determine the table's primary key dynamically, so that I'd have a query like this: $sql = sprintf("UPDATE %s SET %s=%s WHERE PRIMARY_KEY = %s", ...); Is there a M...

When would you use GUIDs as primary keys?

Possible Duplicate: Advantages and disadvantages of GUID / UUID database keys Are there any circumstances where it is essential to use GUIDs as primary keys in a SQL Server 2005/8 DB. For example, does the use of the MS Sync Framework force this, or data replication? ...

Sql Server: uniqueidentifier plus integer compound PK ... what type of index to use?

I have a junction table in my SQL Server 2005 database that consist of two columns: object_id (uniqueidentifier) property_id (integer) These values together make a compound primary key. What's the best way to create this PK index for SELECT performance? If the columns were two integers, I would just use a compound clustered index (...

How can I insert into tables with relations?

I have only done databases without relations, but now I need to do something more serious and correct. Here is my database design: Kunde = Customer Vare = Product Ordre = Order (Read: I want to make an order) VareGruppe = ehm..type? (Read: Car, chair, closet etc.) VareOrdre = Product_Orders Here is my SQL (SQLite) schema: CREATE ...

How do I return the last primary key of the last added item in java?

Hi all. Have a linking (or ref) table which has a dual primary key. Need to return the last primary key which was auto generated from the current connection in a selected table. This is in java. The database is in MS Access if that makes a difference. Sorry for the rushed description, but our team is working to a tight schedule. Any l...

Database Design and the use of non-numeric Primary Keys

Hello, I'm currently in the process of designing the database tables for a customer & website management application. My question is in regards to the use of primary keys as functional parts of a table (and not assigning "ID" numbers to every table just because). For example, here are four related tables from the database so far, one ...

Fluent NHibernate Automap Joined Sub-Class Setting the Key

When automapping a joined subclass in fluent nhibernate, I can't figure out how to give the joined subclass a primary key. public class Address:Entity { public virtual string Address1 { get; set; } public virtual string Address2 { get; set; } public virtual string City { get; set; } public virtual string State { get; set; } public ...

Is it possible to change the value of an auto-increment integer primary key field?

If I set a database column as an autonumber primary key field, will the autonumbering feature prevent those values from being modifiable? Or does the primary key nature of the column make it unmodifiable? Both? Neither? ...

What data type is recommended for ID columns?

I realize this question is very likely to have been asked before, but I've searched around a little among questions on StackOverflow, and I didn't really find an answer to mine, so here goes. If you find a duplicate, please link to it. For some reason I prefer to use Guids (uniqueidentifier in MsSql) for my primary key fields, but I rea...

Reassigning the values of a table PK randomly

Which is the easiest way to perform the following in MySQL 5.1? I have a table with a primary key as an integer, currently running from 1 to 220. The PK runs sequentially depending on the order in which the rows were written to the table. I want to be able to randomly reassign this primary key value, so that, for example, row 1 (with a...

Mysql Index Being Ignored

EXPLAIN SELECT * FROM content_link link STRAIGHT_JOIN content ON link.content_id = content.id WHERE link.content_id = 1 LIMIT 10; +----+-------------+---------+-------+---------------+------------+---------+-------+------+-------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+...

Handling creation of ORM objects prior to persistence/generation of primary keys?

Bear with me as I try to simplify my issue as much as possible. I am creating a new ORM object. This object has an auto generated primary key which is created on the database using as an identity. Within this object, is a child object with a many to one relationship with the parent object. One of the attributes I need to set to create t...

Make one ID with auto_increment depending on another ID - possible?

I want to make a small ticket-system for a project server which has several projects. So far the TicketID will be counted globally, i.e. there is a project A and a ticket with TicketID 1 and another project B and the ticket for this project will get TicketID 2 - like this: (TicketID, ProjectID) (1, 1) (2, 1) (3, 1) (4, 2) (5, 2) (6, 3) ...

MySql Primary Key > 900/1000 bytes?

I have a composite primary key, which together turns out to be rather large (~2000 bytes). I have no performance considerations, I just would like a primary key to enforce uniqueness. MySql doesn't like long primary keys. Is there a way around this? Perhaps to only enforce uniqueness, without building an index? I wouldn't want to use A...

SQL stored procedures design issue

I've lately seen a database where there was a table Types with columns Id, Key and Name. Id was just an Id of the type, Key was a short key name for the type, for example "beer", and the Name was text that could be displayed for the user (for example, "Our greatest beers"). Id was of course unique and was a primary key for this table....

Is it possible to easily detect or predict a Primary Key violation in SQL?

My issue is that I want to run a query to check if the data I am using will be a duplicate of something already in the database, for example I have a Product Code and want to stop the same item being listed multiple times, and this Product Code is the Primary Key. Can I avoid doing this: SELECT ProductCode FROM tblProducts WHERE Product...

Sql Server 2005 Primary Key violation on an Identity column

Hi everyone, I’m running into an odd problem, and I need some help trying to figure it out. I have a database which has an ID column (defined as int not null, Identity, starts at 1, increments by 1) in addition to all the application data columns. The primary key for the table is the ID column, no other components. There is no set of d...