composite-primary-key

Sqlite primary key on multiple columns

What is the syntax for specifying a primary key on more than 1 column in SQLITE ? ...

how to alter table Composite primary key

CREATE TABLE [dbo].[INVS_ITEM_LOCATIONS] ([DEPARTMENT_CODE] [varchar](3) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [IM_INV_NO] [numeric](10, 0) NOT NULL, [LOCATION_CODE] [varchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [CURR_QTY] [numeric](10, 0) NOT NULL CONSTRAINT [DF__INVS_ITEM__CURR___1352D76D] DEFAULT ((0)), [DO...

SQL Server - Noob Relationships Question

I have two tables: CREATE TABLE InmarsatZenith.dbo.ClientJob (JobRef int PRIMARY KEY NOT NULL, IntRef uniqueidentifier, CopyDeadline datetime, PublicationDate datetime, Repeat bit, BusinessType nvarchar(25), Sector nvarchar(30), Lang nvarchar(15), Format nvarchar(25), CreativeRotation nvarchar(50), TipinType nvarchar(25)) and CREATE ...

Composite key or New Unique key

I want to know which will be the best. I am having a table say A having col1(is a foreign key fk1),col2(is also a foreign keyfk2). Can I use PK as (col1,col2) or a newly generated PK. The queries will have fk1 in criteria and fk2 in joins. Kindly suggest me a best one. P.S I am using mysql ...

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? ...

JPA 2.0 Eclipse Link ... Composite primary keys

I have two entities(actually more but it doenst matter) Exam and Exam_Normals Its a oneToMany relationship... The problem is that i need a primary key for Exams_Normals PK (Exam_ID Item) Item should be 1 2 3 4 5 etc.... But cant achieve it getting errors An alternative would be to: I cound use an IDENTITY and a ManyToOne relationship ...

How to update primary key

Here is my problem: I have 2 tables: 1.WORKER, with coloumns |ID|OTHER_STAF| , where ID is primary key, and 2.FIRM, with coloumns |FPK|ID|SOMETHING_ELSE| , where combination FPK and ID make primary key, and also ID is a foreign key referenced to WORKER.ID (not null, and must have same value as in WORKER). I want to make stored proced...

Timestamp as part of composite primary key?

I get this error when using linq-to-sql with timestamp as part of a composite primary key: "The primary key column of type 'Timestamp' cannot be generated by the server." I'm guessing this may be due to the fact timestamp is just a row version thus perhaps it must be created after the insert? Or... ...

Ruby On Rails multiple composite primary keys question

I am a new guy in Ruby, and I have tables with these primary keys: transaction_types: transaction_type transaction_headers: transaction_type transaction_year transaction_id transaction_details: transaction_type transaction_year transaction_id city_id ticker_id tickers: city_id ticker_id Of course, those models have other non ...

SQL Server 2005: Getting error when setting auto increment on one of two primary keys

hi I am getting an error "Cannot insert explicit value for identity column in table 'table' when IDENTITY_INSERT is set to OFF." The table has two primary key, one is set to auto-increment, one is set with value i passed into. Is this possible in SQL Server 2005? or am i missing something? Thanks alot in advance. ...

Nhibernate foreign key as composite key

Hi I am pretty new to Nhibernate and I am working on a project that is described as follow: 1 "Parents" table containing ParentName and a one to many ChildrenList using IList, with Primary Key set to ParentId 1 "Children" table , with a composite primary key that contains ParentID as one of the key so I set up a one to many in Parents...

why must a primary key be minimal?

A primary key is an attribute or set of attributes that uniquely identifies a row in a table. But a primary key has not only to be unique, but also minimal. Why is that necessary? ...

Why do I read so many negative opinions on using composite keys?

I was working on an Access database which loved auto-numbered identifiers. Every table used them except one, which used a key made up of the first name, last name and birthdate of a person. Anyways, people started running into a lot of problems with duplicates, as tables representing relationships could hold the same relationship twice o...

SQL Server 2005 error retrieving record based on Datetime primary key column

Hi All, I have a table in SQL Server 2005 with composite primary key, i.e. a foreign key (INT) and DateTime column. When i run the SQL, Select column1 From TABLE where ForeignKey=1 and DateTime='08/26/2010 11:24:36' No record was return when there is such a record. One option to do is Select column1 From TABLE where ForeignKey=1...

Hibernate: Where do insertable = false, updatable = false belong in composite primary key constellations involving foreign keys?

When implementing composite primary keys in Hibernate or other ORMs there are up to three places where to put the insertable = false, updatable = false in composite primary key constellations that use identifying relationships (FKs that are part of the PK): Into the composite PK class' @Column annotation (@Embeddable classes only) or I...

Django - Multiple columns primary key

Hello, I would like to implement multicolumns primary keys in django. I've tried to implement an AutoSlugField() which concatenate my columns values(foreignkey/dates) ... models.py : class ProductProduction(models.Model): enterprise = models.ForeignKey('Enterprise') product = models.ForeignKey('Product') date = models.Dat...

How to use Oracle Indexes

I am a PHP developer with little Oracle experience who is tasked to work with an Oracle database. The first thing I have noticed is that the tables don't seem to have an auto number index as I am used to seeing in MySQL. Instead they seem to create an index out of two fields. For example I noticed that one of the indexes is a combinati...

Creating index/pk in a huge table is taking too long. I am using Oracle. How do I know if it is going well?

Hi, I have a really huge table, with ~200 million rows. It had no index/pk at all. Selects in this table were (obviously) running slow. I decided to create a PK using 3 columns. I did it in a test environment that has a smaller version of this table and it worked like a charm. So, before going home i did a ALTER TABLE HUGETABLE ADD CO...

Foreign key relationship with composite primary keys in SQL Server 2005

I have two tables Table1(FileID,BundledFileID,Domain) and Table2(FileID,FileType,FileName). In Table2 FileID and FileType are the composite primary key. I want to create a foreign key relationship from Table1.FileID to Table2. Is it possible to do this? Then how plz help. I am using SQL Server 2005 ...

JPA/Hibernate: What's better for composite primary keys, @IdClass or @EmbeddedId implementations and why?

Hello, what's better for JPA/Hibernate composite primary keys, @IdClass or @EmbeddedId implementations and why? This is an intentionally naive question. I decided to use @EmbeddedId (for whatever reason) and I feel like I made the wrong choice. Dereferencing the embeddedId that contains the column properties is redundant and quite erro...