Composite Primary Key Or Not?

Jul 3, 2006

This is really not a T_SQL question but there's no good category for it so I thought I'd just put it here.

I have a table that will contain a large amoutn of data in one field, and every piece of this data is uniquely identitied by six other attributes, that is, six other fields (e.g. user_ID, type_ID, year, country, state, item_ID). I can either make these six fields into a composite primary key, or add an additonal field (say an identity column) as the primary key and add a unique constraint on these six fields. What are the pros and cons of both designs? The one data field is of nvarchar(2000) type and the table is likely to have 50 million+ rows in a couple of years of real use.

This table is not referenced by any other tables so whatever the primary key is, there's no FK reference. However, there could be FK references to this table in the future. Does the FK possibility make a difference in the design considerations?

View 13 Replies

Composite Primary Keys Versus Composite Unique Indexes

Feb 20, 2007

Hello,

I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.

The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold

1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.

A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.

I would like to have some tips, recommendations and alternatives for what I should do in this case.

View 1 Replies View Related

Convert Composite Primary Key Into Simple Primary Key

Jan 11, 2007

Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.

may i convert Composite Primary key into simple primary key in thr table like this.

Thanks,
Uma"

View 1 Replies View Related

Composite Primary Key

Mar 2, 2004

WHile designing a Database should one go for composite Primary Keys.
Or what are the Pros and Corns of Composite Primary Keys

Thanx

View 5 Replies View Related

When To Use Composite Primary Key

Feb 24, 2014

I've been facing this situation since long but today i am asking here. Suppose i have a following tables;

AdmissionInfo, AdmID, AdmDate, AdmFee etc.

SubjectInfo i.e. SubID, SubName, SubStatus etc.

The Result table is like this:

ResID, AdmID, SubID, TheoryMarks, PracticalMarks, ObtMarks, TotalMarks, ResultTerm, SubPercentage.

ResID is PK, AdmID and SubID are Foreign Keys, right. Now What i want to ask/ learn that Am i suppose to combine ResID, AdmID and SubID as a composite PK or should i simply make the ResID as PK and other 2 be there as FK? When do we really need to use Composite PK and using it a good thing or not?

View 1 Replies View Related

Composite Primary Key

Jul 20, 2005

i have a master table with around 15 columns and i am trying to findthe appropriate primary keys and indexes for the table.To make the records unique, i need to include two datetime columns (start and end dates ) and two integer columns ( attributes of therecord ) to make up a composite primary key. Both of these fourcolumns are found in the WHERE clause of my queries.Is it acceptable in the view of performance and how should i createthe indexes?

View 5 Replies View Related

Composite Primary Key

Aug 4, 2007

Hello,

Does composite primary key affect performance on the table that contains the composite primary key or tables that references this table?

When composite primary key should be used?

View 2 Replies View Related

Creating Composite Primary Key

Feb 2, 2004

Hi

I'm trying to create a composite Primary Key on a table. This is the SQL I've written:

CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)

Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.

Thanks
Jon

View 1 Replies View Related

Composite Primary Keys

Jun 25, 2002

Newbie question...

I have two tables (categories & listings) which create a many-to-many relationship.

I have created an interim table with the primary keys from each table as a composite primary key...(cat_id & list_id).

How does the interim table get populated with the id's?

When I do an insert statement to insert data into the categories table, the cat_id field is automatically generated...same with the listings table, but when (and how) does the primary key data get into the interim table.

Thanks in advance for the assistance.

View 1 Replies View Related

Composite Primary Key Syntax

Nov 27, 2007

I'm trying this

Code:

CREATE TABLE Rating
(ContentID int NOT NULL PRIMARY KEY REFERENCES Content_(ContentID),
UserID int NOT NULL PRIMARY KEY REFERENCES Usr(UserID),
rating tinyint DEFAULT 2,
LastRead smalldatetime NOT NULL DEFAULT CURRENT_TIMESTAMP)

And it is telling me this:

Msg 8110, Level 16, State 0, Line 14
Cannot add multiple PRIMARY KEY constraints to table 'Rating'.

So how do I combine two non-unique foreign keys, the combination of which is unique, into one primary key?

View 2 Replies View Related

Surrogate Or Composite Primary Key?

Aug 23, 2004

My previous post was not really clear, so I'll try again with a (hopefully) better (even if longer) example...

Consider the following...

A JOB describes the processment of a document.
Each document can exist in two versions: English and French.
A JOB can have 1 or 2 TASK, each describing the processement of either the English or French version.
So we have the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

that is there is an identifying 1:M (where maxium allowed for M is 2) relationship between JOB and TASK; TASK being identified by JobNum and Version (where the domain for Version is {E, F}).

Each TASK may require a TRANSLATION sub_task.
Each TASK may require a TYPING sub_task.
Each TASK may require a DISTRIBUTION sub_task.

For example, for a given doc, the English TASK requires TRANSLATION and DISTRIBUTION, while the French only DISTRIBUTION.

That is, there is a 1:1 not-required relationship between TASK and TRANSLATION, TYPING and DISTRIBUTION.
So we have the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

As you can see I am using the PK of TASK as FK and PK for each of the three SUB_TASKs.

To complicate things, each SUB_TASK has one or more assignments. The assignments for each SUB_TASK records different information from the others.
So we have...

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (JobNum [PK] [FKa], Version [PK], Priority, ...)

C: TRANSLATION (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
D: TYPING (JobNum [PK] [FKb], Version [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (JobNum [PK] [FKb], Version [PK] [FKb], Copies, ...)

F: TRA_ASSIGN (JobNum [PK] [FKc], Version [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (JobNum [PK] [FKd], Version [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (JobNum [PK] [FKe], Version [PK] [FKe], Index [PK], Pages, ...)

that is there is an identifying 1:M relationship between each SUB_TASK and its ASSIGNMENTs, each ASSIGNMENT being identified by the SUB_TASK it belongs to and an Index.

I wish I could send a pic of the ER diagram...

Maybe there is another and better way to model this: if so, any suggestion?

Given this model, should I use for TRANSLATION, TYPING and DISTRIBUTION a surrogate key, instead of using the composite key, like for example:

C: TRANSLATION (TranslationID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
D: TYPING (TypingID [PK], JobNum [FKb], Version [FKb], DueDate, ...)
E: DISTRIBUTION (DistributionID [PK], JobNum [FKb], Version [FKb], Copies, ...)

this will "improve" the ASSIGNMENTs tables:

F: TRA_ASSIGN (TranslationID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TypingID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (DistributionID [PK] [FKe], Index [PK], Pages, ...)

I could even go further using a surrogate key even for TASK, which leads me to the following:

A: JOB (JobNum [PK], DocReference, StartDate, EndDate, ...)
B: TASK (TaskID [PK], JobNum [FKa], Version , Priority, ...)

C: TRANSLATION (TaskID [PK] [FKb], DueDate, ...)
D: TYPING (TaskID [PK] [FKb], DueDate, ...)
E: DISTRIBUTION (TaskID [PK] [FKb], Copies, ...)

F: TRA_ASSIGN (TaskID [PK] [FKc], Index [PK], Translator, ...)
G: TYP_ASSIGN (TaskID [PK] [FKd], Index [PK], Typyst, ...)
H: REP_ASSIGN (TaskID [PK] [FKe], Index [PK], Pages, ...)

I don't really like this second solution, but I'm still not sure about the first solution, the one with the surrogate key only in the SUB_TASks tables.

View 2 Replies View Related

Composite Primary Key Constraint

Mar 21, 2007

Hi All,

I have a table that has 3 columns, two of them make a composite primary key. The table is populated with data. What I need to do is to add a third column to a composite primary key. I have tried to do that with the following command:

alter table databasesize
add constraint pk_dbsize primary key (dbid)

But I get the error message:

Table 'databasesize' already has a primary key defined on it.

How can I do this?

View 2 Replies View Related

Composite Primary Key And Foreign Key

Feb 8, 2008

Hi,
In my table1, I have a composite primary key ssn+firstname+lastname.
In table2 I have ssn, firstname, lastname, col1. In table2 I want to create a foreign key which references table1 primary key?
table1 and table2 has the primary, foreign key relationships. Still table2 need primary key. In table2 Can I make the ssn+Firstname+Lastname as primary key? or one column id as auto increment?

Which is best way?

Thanks

View 27 Replies View Related

Composite Primary Key Autoincrement From 1

Jul 12, 2006

Hi,

I'm currently writing a small application that is using SQL Server as a back-end database. A part of my database looks something lie the following:

Tables:
Orders(OrderId[int, PK], OrderDescription[varchar] etc...)
OrderLines(OrderId[int, PK, FK], OrderLineId[int, PK], etc...)

What I need to achieve is - everytime that a new line is inserted into an orderlines table part of the primary key will be the OrderId and the OrderLineId should be auto-incremented from 1 for each OrderId in the OrderLines table.

I know i can do this manually in my program, but i'm just wondering if theres a way to achive this in SQL Server?

Thanks,

Nick Goloborodko

View 3 Replies View Related

Foreign Key Constraint To A Composite Primary Key

Mar 6, 2008

I have two tables: table 1 and table 2. The primary key of table is composite key of two collumns and table 2 is the child of parent table 1. Is it possible to create / define a foreign key constraint using a composite primary key?

View 10 Replies View Related

Composite Primary Key Data Type

Jan 2, 2001

hi, can I have a composit primary key which have more than one data type. for instance job_id int,job_type varchar(20)... thanks

Al

View 1 Replies View Related

Composite Primary Key And Foreign Key - Relations

Sep 19, 2000

Hi,

I have a doubt, can anyone please clarify me.

I have created the following table,

create table z
(
eno int,
deptno char(2),
ename varchar(5),
constraint pk_eno_deptno primary Key(eno,deptno)
)
and I tried to establish a foreign key based on the above defined table. It's giving error,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno),
deptno char(2)
)
go

Error:-
*****
There are no primary or candidate keys in the referenced table 'z' that match the referencing column list in the foreign key 'FK__z_dup__eno__18CE1BA6'.Could not create constraint. See previous errors.

Even I tried to create a foreign key like the following,

create table z_dup
(
eno int FOREIGN KEY REFERENCES z(eno,deptno),
deptno char(2)
)
go

Error:-
********
More than one key specified in column level FOREIGN KEY constraint, table 'z_dup'.

Can anyone please help me to create a foreign key relation with the base table.

tks in advance,
Srinivasan

View 5 Replies View Related

Referencing Composite Primary KEYS

Feb 27, 2006

Hi,
i want to make a reference from a table on itself.
The table has a composite Primary Key. But I just want to refernce the TEstCaseID.
So whats wrong? Can anyone help me?

CREATE TABLE dbo.TestCase (
Project_projectID VARCHAR(20) NOT NULL references Project,
testCaseID VARCHAR(50) NOT NULL,
PRIMARY KEY(Project_projectID, testCaseID),
FatherID VARCHAR(50) references TestCase(testCaseID)

)

THanx Crean

View 2 Replies View Related

Create Foreign Key Using Composite Primary Key

Jul 24, 2014

I am trying to create a FK using a composite PK and here are the details that I want to achieve.

Table -A
Column A1 not null,
Column A2 not null
Primary key (A1, A2).

Table -B
Column B1 Primary key.
Column B2 not null
FK (B2) References A(A1).

When I try to do this I am getting some errors. Questions: First of all is this possible? if yes, then how I can create it.

View 1 Replies View Related

Splitting A Composite Primary Key In A Table

Mar 26, 2008

NOTE:
I am not interested in any responses that want to argue the use of a unique ID field and autonumbering as the PK. It is quite clear from the forums that this subject is a polarizing one. This question is for those who follow text-book design practices and believe that a composite primary key should be used when it is available. I want to be one of them for the time being.

SYNOPSIS:
I have three tables, TestSummary, TestDetails, and Steps.

The TestSummary table looks like this:

Create table TestSummary
(
TestSummaryID int identity primary key,
...
SequenceID int not null
)

It contains the date and time of the test, the serial number, the part number, the test operator's name, and the ID of the sequence of steps used during the test. It uses a unique ID field for the primary key.

The TestDetails table looks like this:

Create table TestDetails
(
TestDetailsID int identity primary key,
TestSummaryID int not null,
StepID int not null,
...
)

It contains the details of the test like voltage readings, current readings, temperature, etc., one record per reading. It also contains the step number of the test sequence specified in the TestSummary table.

The Steps table looks like this:

Create table Steps
(
SequenceID int not null,
StepID int not null,
Function int not null
Primary key (SequenceID, StepID)
)

It contains a list of all of the functions to be performed on the device under test by sequence number and the step number within the sequence.

When I try to establish a relation between TestSummary.SequenceID and Steps.SequenceID, SQL Server flags an error because TestSummary.SequenceID and Steps.SequenceID:Steps.StepID do not match.

What is the problem with this approach?

View 1 Replies View Related

Clustered Index On Composite Primary Key

Jul 23, 2005

Is that possible on SQL Server 2000 and onwards?

View 1 Replies View Related

Composite Primary Key On A Table Variable?

Jul 20, 2005

Is is possible to create a composite primary key on a table variable?Neither of these two statements are successful:DECLARE @opmcjf TABLE (jobdetailid INT NOT NULL,cjfid INT NOT NULL,cjfvalue VARCHAR(100) NULL)ALTER TABLE @opmcjf ADD CONSTRAINT [PK_opmcjf] PRIMARY KEY CLUSTERED([jobdetailid],[cjfid])andDECLARE @opmcjf TABLE (jobdetailid INT PRIMARY KEY,cjfid INT PRIMARY KEY,cjfvalue VARCHAR(100) NULL)Thanks,Shaun

View 2 Replies View Related

Insert Or Update SSIS For Composite Primary Key

Sep 1, 2006

Hi ,

We have scenario like this .the source table have composite primary key columns c1,c2,c3,c4.c5,c6 .when we move the records to destination .we have to check columns (c1+ c2 + c3 + c4 + c5 + c6) combination exist in the destination. if the combination exist then we should do a update else we need to do a Insert . how to achive this .we have tryed useing conditional split which is working only for a single Primary key . can any one help us .

Jegan.T

View 8 Replies View Related

Composite Primary Key Using MS SQL Server Enterprise Manager Interface

Jan 24, 2008

I want to created composite primary key using MS SQL Server Enterprise Manager Interface. I want to use Interface instead of writing it in CREATE Table statement. I was able to create foreign key using this Interface using the "Manage Relationship" option. But cannot find how to add primary key consisting of two fields. Any help regarding this is highly appreciated.

View 2 Replies View Related

How Do I Create A Composite Primary Key, Using Fields From Multiple Tables?

May 22, 2007

Hi All

I hope someone smart can help me, it would be highly appriciated.

I am developing an SQL Serverdatabase and in on of the tables I need the primary key to consist of two pieces of data.

TblOrders: OrderNum, Orderdate, ....

TblDispatchers: DispatcerID, Dispatcher, DispatcherAddress

The OrderNum field in TblOrders need to be a composite of an AutoNum-field (incrementet by 1) and the DispatcerID from the tblDispatchers.

Can this be done, and how.

Many thanks

Kind regards

Tina

View 3 Replies View Related

How To Find Missing Records From Tables Involving Composite Primary Keys

Nov 23, 2006

Table 1

     Code
    Quarter

500002
26

500002
27

500002
28

500002
28.5

500002
29

Table 2

     Code
           Qtr

500002
26

500002
27

I have these two identical tables with the columns CODE & Qtr being COMPOSITE PRIMARY KEYS

Can anybody help me with how to compare the two tables to find the records not present in Table 2

That is i need this result

    Code
   Quarter

500002
28

500002
28.5

500002
29

I have come up with this solution

select scrip_cd,Qtr,scrip_cd+Qtr from Table1 where
scrip_cd+Qtr not in (select scrip_cd+qtr as 'con' from Table2)

i need to know if there is some other way of doing the same

Thanks in Advance

Jacx

View 3 Replies View Related

Composite Key?

Sep 23, 2006

I'm just now learning both SQL and ASP.NET, and I cannot seem to figure out how to build my data structure. I believe the answer to my problem is a composite key, but I cannot seem to get it to work. Here is an example. My database is of recorded dances, with exact locations within a ballroom. I believe I need 2 tablesTable #1 - DanceTableColumns: DanceID, Name, Description, TagsTable #2 - StepsTableColumns DanceID, StepID, longLocation, latLocation, Action, DescriptionWithin my ASP.NET application I want to be able to enter data about a dance, including metadata and a series of steps. The Dance and metadata content to be stored in DanceTable, and the series of moves stored in the StepsTable. I want the steps to be IDed as 1, 2, 3, 4...x with the first step being labled 1. and I want each dance to have it's own unique ID (DanceID). Right now I'm using "ExecuteNonQuery()" to add my data to my SQL database, and when I add new steps to the StepsTable SQL just finds the largest ID within StepID and increments it by one. So my steps are labeled as:Dance1:Step1, Step2, Step3, Step4Dance2:Step5, Step 6, Step7What I really want is (or I think what I want is) is a composite primary key.Dance1:Step1, Step2, Step3, Step4Dance2:Step1, Step2, Step3That way the StepID is used as both a primary key and it indicates the position within the dance. I guess I could just use a standard SQL table, let SQL auto generate StepID's and then add a new column called something like "StepNumber", but it just seems goofy to be generating a stepID and never using it. With composite keys (If I understand them) each step would have a unique key as a combination of the DanceID+StepID (AKA Dance 345, steps 1-10). I pull up data by searching for dances, and then sort by StepNumber, and those should all be unique...if I can figure out how to build them.

View 1 Replies View Related

Composite Key NOT IN Query?

Nov 9, 2006

I have a table with a composite key formed by the unique combination of columns w, x, y, z
I'm trying to write an INSERT statement along the following lines
INSERT INTO myTable
(SELECT w, x, y, z FROM someTable) t1
WHERE (this is the part I'm stumped on - where the unique combination of w, x, y, z is NOT in myTable already)
Help would be appreciated. Can you use the NOT IN keyword on composite values?

View 2 Replies View Related

Add Composite Key Thru SQL Server GUI?

May 29, 2008

How can I create a composite key (two primary keys for one table) using SQL Server 2005 Management Studio? Can I do it using the GUI, or do I have to write SQL code to do this? Thanks

View 7 Replies View Related

Composite Key Cannot Allow Bit Column

Apr 19, 2004

Made an interesting discovery today - a column of data type Bit is not allowed to participate in composite key. Rather surprised.

I have a certain unique code that exhibits 2 unique states, which rendered the use of the boolean column, so the uniqueness goes <code>-1 and <code>-0. Is there any 'hack' so to speak to avoid using a Tinyint for the boolean column instead?

View 5 Replies View Related

Use Of Composite Indexes

Jun 10, 2005

Hi,

Suppose there is a composite index on a table, and in includes, for example 3 columns.

If I do a select using one of those three columns, will the select use the composite index? Will it aid in retrevial or should I create an index on the
individual column.

any thoughts on this would be appreciated.

thanks

View 2 Replies View Related

Creating A Composite Key

Sep 24, 2004

Can someone help me create a composite key on ms sql server through the enterprise manager?

Thanks,
Laura

View 1 Replies View Related

Surrogate Or Composite Key?

Aug 21, 2004

The orininal design of my db (part of it...) is the following

A JOB has a Number and a Description.
Each JOB can have one or two TASKS (min one, max two). Each TASK is identified by the JOB it belongs to and an Index (unique only for the same JOB).
Each TASK has one an only one set of INFO1, one and only one set of INFO2, one and only one set of INFO3 etc.

A: JOB (JobNum [PK], JobDescription, ...)
B: TASK (JobNum [PK] [FKa], Index [PK], TaskDescription, ...)
C: INFO1 (JobNum [PK] [FKb], Index [PK] [FKb], ...)
D: INFO2 (JobNum [PK] [FKb], Index [PK] [FKb], ...)

(There is a reason to keep INFO1, 2 and 3 separate, because eachof them will be linked to different table. This might influence the answer to my real question.)

First of all, I wouldn't add any surrogate key for TASK, not to loose the logic behind; plus I'd put an ined on JonMum only, being Index equal to 1 or 2 only, so not selective.

The real question is about INFO1 (and 2, 3 etc.) table: should I leave JobNum and Index as PK (consider that the PK of INFo1 will be used as FK for another table), or should I use a surrogate key, like for eaxmple

C: INFO1 (Info1ID [PK], JobNum [FKb], Index [FKb], ...)

I don't really like this solution. Actually I'd prefer the following

C: INFO1 (Info1ID [PK], ...)

where Info1ID = JobNum + Index (+ = string concatenation).

Any suggestion?
Thanks

View 3 Replies View Related