primary-key

MySQL: Auto incrementing combined keys

Ok, I have a table that will look something like this: Post ˪ Id ˪ Version ˪ Title ˪ Content The idea is that the Id and Version together will be the primary key since you can have a single post multiple times but of different versions. My question is this: I would like to have the Id to auto increment. Is this possible in a setu...

SQLAlchemy with multiple primary keys does not automatically set any

I had a simple table: class test(Base): __tablename__ = 'test' id = Column(Integer, primary_key=True) title = Column(String) def __init__(self, title): self.title = title When using this table, id was set automatically. I want to add another field that is unique and efficient to search, so I added the field: ...

How to have a primary key with null values using empty string ?

I have a table in which I need both the values to be primary because I am referencing this combination as foreign key in the other tables. Table definition and the data I need to put are as follows create table T1 ( sno number(10), desc varchar2(10), constraint T1_PK primary key(sno,desc) ) DATA to put sno | desc -------------...

Is a primary key automatically an index?

If I run Profiler, then it suggests a lot of indexes like this one CREATE CLUSTERED INDEX [_dta_index_Users_c_9_292912115__K1] ON [dbo].[Users] ( [UserId] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF) ON [PRIMARY] UserId is the primary key of the table Users. Is this index better than the...

Better alternative to autonumber primary keys

I am looking for a better primary key than the autonumber data type, namely for the reason that it's limited to a long integer, when I really just need the field to reflect a number or text string that will never ever repeat, no matter HOW many records are added or deleted from the table. The problem is I am not sure how to implement so...

Composite primary key scenario

I need a many to many structure, but with an aggregate constraint. What i'm trying to accomplish is done easily in pure sql, but since ActiveRecord discourages composite primary keys, i'm not sure how to accomplish what i need in recommended style. Here is what i would have in pure sql: table Project (ID int) table Report (ProjectWide...

How To Disable Subsonic's Primary Key Autoincrement?

Hi everybody, I'm using Subsonic (simplerepository) and SQLite, and I have a class with an Int64 property marked as [SubSonicPrimaryKey]: [SubSonicPrimaryKey] public Int64 MyID; which is transformed into: [MyID] integer NOT NULL PRIMARY KEY AUTOINCREMENT Is it possible to disable the AUTOINCREMENT feature? ...

Problem with MultiColumn Primary Key

DataTable NetPurch = new DataTable(); DataColumn[] Acct_n_Prod = new DataColumn[2]; DataColumn Account; Account = new DataColumn(); Account.DataType = typeof(string); Account.ColumnName = "Acct"; DataColumn Product; Product = new DataColumn(); Product.DataType = typeof(string); Product.ColumnName = "Prod"; NetPurch.Columns.Add(Accoun...

Entity Relationships - Can a weak entity take part in a 'one to many' relationship as the 'one'

Hi, With the following entity relationship structure I'm struggling to figure out if the relationship between the LOAN and ITEM entities is valid? The weak entity of LOAN uses a partial key of 'loan_dateLeant' and the primary keys from CUSTOMER and ITEM to form LOANs primary key. However LOAN has a 'one to many' relationship with ITEM ...

Custom keys for Google App Engine models (Python)

First off, I'm relatively new to Google App Engine, so I'm probably doing something silly. Say I've got a model Foo: class Foo(db.Model): name = db.StringProperty() I want to use name as a unique key for every Foo object. How is this done? When I want to get a specific Foo object, I currently query the datastore for all Foo objects ...

How to definie a VB.NET DataTable Column as primary key after creation

Hello I am importing Tables from a Oracle DataBase, using a VB.NET dataAdapter. I use the "fill" comand to add the imported data to a DataSet. How is it possible to define a specific column of a DataTable as PrimaryKey, after the DataTable is already filled with data? Thank your for your help. ...

Negative Primary Keys

Are there any repercussions using Negative Primary Keys for tables (Identity Increment -1, Identity Seed -1 in SQL Server 2005)? The reason for this is we're creating a new database to replace an existing one. There are similar tables between the two databases and we'd like the "source" of the information to be transparent to our appli...

DB Designer creates compound primary key

When adding relationships to a database model in DB Designer 4, a composite primary key is being created every time. So every foreign key I add, I get an extra key added to a composite primary key. I think I must have changed a setting as I don't remember it doing this in the past. Does anyone know how to turn off this feature as I pr...

Foreign key reference to a two-column primary key

I'm building a database that must work on MySQL, PostgreSQL, and SQLite. One of my tables has a two-column primary key: CREATE TABLE tournament ( state CHAR(2) NOT NULL, year INT NOT NULL, etc..., PRIMARY KEY(state, year) ); I want a reference to the tournament table from another table, but I want this reference to b...

GUID or int entity key with SQL Compact/EF4?

This is a follow-up to an earlier question I posted on EF4 entity keys with SQL Compact. SQL Compact doesn't allow server-generated identity keys, so I am left with creating my own keys as objects are added to the ObjectContext. My first choice would be an integer key, and the previous answer linked to a blog post that shows an extension...

MySQL - What is the correct primary key method

I'm starting to develop an application using MySQL and although I've developed apps before using databases, I've normally gone to the incrementing id method. The other day I was reading a posting somewhere and saw someone being picked apart for this, for not properly "normalising the database". I'm not a big database person, but I wanted...

Primary Key Identity Value Increments On Unique Key Constraint Violation

I have a SqlServer 2008 table which has a Primary Key (IsIdentity=Yes) and three other fields that make up a Unique Key constraint. In addition I have a store procedure that inserts a record into the table and I call the sproc via C# using a SqlConnection object. The C# sproc call works fine, however I have noticed interesting results ...

Should foreign keys become table primary key?

Hello again, I have a table (session_comments) with the following fields structure: student_id (foreign key to students table) session_id (foreign key to sessions table) session_subject_ID (foreign key to session_subjects table) user_id (foreign key to users table) comment_date_time comment Now, the combination of student_id, session...

Change an access Primary key with relationships

I have a database in access 2007 accdb extension , there are more or less 30-40 tables with related primary key "local_number". it is a text primary key with 10 lenght. How can I change the length of this primary key to 30 WITHOUT delete previosly all the 30 relationship. A similar question. I need add a compose primary key to my PK ...

how do indices in mysql tables (MyISAM) work?

Few basic doubts I have: 1. Is primary key column automatically indexed? 2. What should be criteria to select index column? 3. When should I club multiple columns? 4. Does MyISAM or InnoDB has any affect on which columns should be indexed? Probably not. 5. Are they really required, specially in case if primary key column is automati...