primary-key

Should I initialize my AUTO_INCREMENT id column to 2^32+1 instead of 0?

I'm designing a new system to store short text messages [sic]. I'm going to identify each message by a unique identifier in the database, and use an AUTO_INCREMENT column to generate these identifiers. Conventional wisdom says that it's okay to start with 0 and number my messages from there, but I'm concerned about the longevity of m...

Creating a Primary Key on a temp table - When?

I have a stored procedure that is working with a large amount of data. I have that data being inserted in to a temp table. The overall flow of events is something like CREATE #TempTable ( Col1 NUMERIC(18,0) NOT NULL, --This will not be an identity column. ,Col2 INT NOT NULL, ,Col3 BIGINT, ,Col4 VARCHAR(25) NOT NULL, ...

Use composite keys? Or always use surrogate keys?

Duplicate: Many to Many Relation Design - Intersection Table Design If I have these tables (* = primary key): user id* name group id* name Is this better? user_group user_id* group_id* Or is this better? user_group id* user_id group_id ...

How do I use Navigational Properties as Primary Keys in Entity Framework (.Net 4.0)?

I'm trying out the Model First approach introduced in Entity Framework with VS2010/.Net 4.0, and now I don't really know how to do this. I start out with the following Entities: Contact Event ******* ***** Id (Int32, not null, pk) Id (Int32, not null, pk) Name (Name, not null)...

SQLSERVER: How to alter an existing table int primary key to become an identity column?

My database has a table with thousands of records. The primary key is an integer. There's a lot of foreign key constraints associated with this column. I want to change this column to become an identity key. What's the best way to do it? I also need to send this update to our clients installations. Bonus points for an answer that work...

How to prevent primary serial primary key from being updated with number not in sequence?

CREATE TABLE u_account ( Jid serial primary key, score int4 ); The primary key works fine (updates itself) ok when I update it like this; INSERT INTO u_account ('score') VALUES ('122233344'); However when I insert a value like this; INSERT INTO u_account VALUES ('122233344'); This updates the primary key; I don't want the primar...

Can Primary-Keys be re-used once deleted?

0x80040237 Cannot insert duplicate key. I'm trying to write an import routine for MSCRM4.0 through the CrmService. This has been successful up until this point. Initially I was just letting CRM generate the primary keys of the records. But my client wanted the ability to set the key of a our custom entity to predefined values. Potential...

SQL: How to make sure any primary key in any table "belongs to" a known Account ID (also PK) at the top of the hierarchy?

Problem as follows: I'm in charge of a creating a web services interface to a system with a huge database (hundreds of tables). The top level table in the database is "Accounts", with primary key Account_Id. Each row in any table can ultimately be traced back to a single account. Each account should have access to their own account v...

Should I use a nullable int when working with int object IDs?

If I'm working with, say, a BookID of type int in my code that corresponds to an int primary key field in a database table (I'm using C# and SQL Server)... is it best practice, when I'm passing around IDs in my code, to use a nullable int and check for null to see if the BookID exists: if (BookID != null) { or, is it a better practice...

does the order a composite key is defined matter?

I have a table with (col1,col2) as a composite primary key. create table twokeytable(col1 int,col2 int,constraint twokeytable_pk primary key (col1,col2)); and another table with col3,col4 collumns witha composite foreign key(col3,col4) which references the(col1,col2) primary key. For some processing I need to drop the foreign key an...

Editable multicolumn primary key with ASP.NET's GridView and SqlDataSource

I have a table TaskSchedule. It has a three-part primary key and two more values: IdTask PK IdPerson PK StartTime PK EndTime Remarks IdTask and IdPerson are also foreign keys. The idea is that you can write down which person worked on which task in which period of time. We suppose that those three stuff will be sufficient to specify u...

How do I specify a Primary Key in Sqlite

How to define your specified attribute like StudentId in student table as Primary key in sqlite ...

How can define a composite primary key in SQL?

How can I define a composite primary key consisting of two fields in SQL? I am using PHP to create tables and everything. I want to make a table name voting with fields QuestionID, MemeberID, and vote. And the QuestionID and MemberID will be the primary keys. How should I do this? ...

slow update (primary key)

update auditdata set TATCallType='12',TATCallUnit='1' from auditdata auditdata inner join Auditdata_sms_12 a_sns on auditdata.ID = a_sns.id when I above query it takes more than 10 minutes to execute. what wrong in this Auditdata.ID is primary key.. if i run Update command is that also update the indexes??? is this reason...

Oracle - return newly inserted key value.

We have a table with a Primary Key that gets populated on insert by a trigger on the table - the trigger gets the next sequence number from a sequence we created for the table and uses that for the value of the key on insert. Now we would like to be able to return that value in our insert procedure (PL\SQL), similar to select @@scope_ide...

I need to auto_increment a field in MySQL that is not primary key

Hey everyone, Right now, I have a table whose primary key is an auto_increment field. However, I need to set the primary key as username, date (to ensure that there cannot be a duplicate username with a date). I need the auto_increment field, however, in order to make changes to row information (adding and deleting). What is normally ...

Primary Keys - Native, Sequence, or GUID keys?

In reading this and this and then reading this (which references the other two ironically) I find myself wondering just how big the discussion of this topic is? I am a SQL Server guy and so I tend to use an Identity that is auto generated in the form of an int. However, when I know that I will need some form of replication between serv...

Inserting records into an XML table with a unique key

Hi, Maybe I'm just spoiled by SQL, but is there some cool LINQ way to insert a new record into an "XML table" and have the indexed ID update automatically (or at least semi-automatically)? Here's what I mean by "XML table": <myElements> <myElement> <id>1</id> <value>blah</value> </myElement> <myElement> <id>...

Should I use a composite key for a map table, which is also used for a foreign key?

I am using ASP.NET and the Entity Framework to make a website. I currently have a map table for a many to many relationship between... let's say users and soccer teams. So: Users Teams UserTeams Part 1: Is it best practice to use a composite key for the primary key of the map table? In other words: UserTeams table PK UserId PK Te...

SubSonic 3 and multiple PK columns

I'm having problem with SubSonic 3 and multiple Primary Key columns. It seems like it isn't supported via the ActiveRecord T4 script. In line 173 of Settings.ttinclude return this.Columns.SingleOrDefault(x=>x.IsPK) ?? this.Columns[0]; It tries to get a Single Primary Key Column and failed. Any solutions? ...