Compound Primary Key - How To ?

Nov 25, 2003

I need to know how to create a compound primary key in Transact-SQL


It's really simple, it's a table with 3 column, two of those make the primary key Right now the CREATE TABLE look like this:/* SectionsContent Table */


CREATE TABLE SectionsContent (


SectionID Int NOT NULL


CONSTRAINT FK_SectionsContent_SectionID FOREIGN KEY


REFERENCES PsychoCMS.dbo.Sections(ID),


DocumentID Int NOT NULL


CONSTRAINT FK_SectionsContent_DocumentID FOREIGN KEY


REFERENCES PsychoCMS.dbo.Documents(ID),


Position int NOT NULL


)





Can anyone help me ?

View 1 Replies


ADVERTISEMENT

Compound Primary Key - Order Not As Expected

Apr 26, 2006

Hello,if you create this table:create table hello (int a, int bconstraint pk_hello primary key clustered ( a, b ))and then insert the following recordsa,b1,11,21,32,12,22,33,13,23,3and then doselect a,b from hellothe output seems to be:a,b1,12,13,11,22,23,21,32,33,3which is wrong and (i think) is reflecting the actual index orderand physical order on diskit should be:a,b1,11,21,32,12,22,33,13,23,3i have tested this on a table with 500,000 recordsand sure enough if you declare the clustered primary key fields inreverse order:constraint pk_hello primary key clustered ( b, a )two things happen:- the select with no order by returns the records in the expected order- queries relying on that order run MUCH FASTERhas anyone else seen / noticed this?

View 9 Replies View Related

Passing Compound Primary Key Values To A Stored Procedure Using ADO.NET

Apr 10, 2008

Hi all,
I've had this problem for a while now and I'm looking for a better solution.


I'm pulling through a dataset from a SQL Server 2005 database and populating it into a DataGridView. The end user updates information on the grid and I then want to updates the results in the SQL Server table. My table has a compound primary key made up of two fields.


I'm currently looping through the data grid and for each value identified to update I'm executing a stored procedure that takes the two fields as parameters. However I know that this method isn't very efficient, especially if the user wants to update a few hundred records as it will execute the stored procedure a few hundred times.


Is there a way I can pass the two parameters in an ArrayList or something like that? I need SQL Server to be able to take the parameter. Is XML the way to go or is there any additional support in the .NET framework for such problems?


Thanks for your help.

View 7 Replies View Related

Compound Statements

Jul 20, 2005

Hello,How can I stop/prevent SQL server from running compound SQLstatements. I do not want the server to run multipleupdate/delete/insert/select statements as a batch. Is there an option?/Kafwww.afiouni.com

View 11 Replies View Related

Utilizing Compound Indexes

Jul 23, 2005

Is it possible to force the use of a compound index in a query?create table Test (ColOne int, ColTwo int)The compound index is ColOne + ColTwo.I'm interested in searching on ColTwo, but I also know the value ofColOne will always be the number "1".How do you structure the SQL statement to concatenate the two INTs anduse the index? Note that I don't have any control over the creation ofthese indexes.

View 3 Replies View Related

Confused Exec With A Compound Sql String

Aug 1, 2000

I am trying to make up a SQL string which will be executed with the Exec command

I want to add a return column that is not in the table, and the table columns:

something like
Select @Ten As Ten, @Tablename.* From @Tablename (
where @Ten has an integer value. )
The statement was originally:
select @SQL = 'select * from ' + @TempName
exec (@SQL)
which had no problem. Then I needed to add an extra column of static data to all the returned rows, and confusion !!!!!

Thanks,
Judith

View 1 Replies View Related

Generating Values As Part Of A Compound Key

Aug 30, 2005

BEGINNER QUESTIONI have a table which has a compound primary key consisting of two columns.One of these columns is a foreign key which is generated in another table byan identity.I want to be able to generate the other primary key column valueautomatically when an insert occurs but assume that I cannot use an identitybecause it would have to be unique for this table.There will be potentially more than one user accessing this table so I wantto avoid generating the key on the client side.How can I do this? Will it require some hardcore T-SQL?I hope this is clear (I suspect it isn't) I'd be happy to supply more info.I would be extremely grateful for any help!Mark.

View 4 Replies View Related

Compound Return On A Rolling Daily Basis

Nov 2, 2014

Looking to create a query, as simple as possible, that allows me to compound returns on a rolling daily basis. So far this this have I have:

DECLARE @stock_returns TABLE
(
stock_code VARCHAR(10) NOT NULL,
date1 DATE NOT NULL,
daily_return NUMERIC(10, 2) NOT NULL
);

[Code] ....

But I´m not getting what I need. If you run the above select, the output should be:

stock_codedate1daily_returnLAGCompound_return
stock12014-07-080.00510 0.00000 0.0051000000
stock12014-07-090.00300 0.00510 0.0081153000
stock12014-07-100.00500 0.00300 0.0080150000
stock12014-07-110.00600 0.00500 0.0110300000
stock12014-07-120.00200 0.00600 0.0080120000
stock12014-07-130.00700 0.00200 0.0090140000
stock12014-07-140.00240 0.00700 0.0094168000
stock12014-07-150.00240 0.00240 0.0048057600
stock12014-07-160.00250 0.00240 0.0049060000

The problem is with this column:

(lag(daily_return, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

The (daily_return + 1) portion should be the accumulated compound return. So it should be something like

(lag(ACCUMULATED_COMPOUND RETURN, 1, 0) over (order by date1) + 1) * (daily_return + 1) - 1 as Compound_return

And the output should be:

Date1Daily returnLAGCompound Return
08/07/20140,00510,00000,0051
09/07/20140,00300,00510,0081
10/07/20140,00500,00300,0132
11/07/20140,00600,00500,0192
12/07/20140,00200,00600,0213
13/07/20140,00700,00200,0284
14/07/20140,00240,00700,0309
15/07/20140,00240,00240,0334
16/07/20140,00250,00240,0359

View 10 Replies View Related

Full Text Catalog, Compound Key Problem

Dec 19, 2007

Hi

I have a sql 2000 db which has a table that has a compound key, the problem is that I would like to create a Full Text Catalog for this table. However I noticed that i need a single primary key... but I dont have one.

I created another field on my table called "ftcID" as an int with identity set to Yes

However when I try and create a catalog it doesnt detect that this field is unique.

Does my unique field have to be a Primary Key, I cant remove the compound primary key as it will break my application.

Any help would be much appreciated

Many thanks in advance

View 3 Replies View Related

SSIS SCD Type I, Dim Table Compound Key Selection For Business Key

Apr 28, 2006

Hai,

I have been working in DW for a while, but using SSIS as an ETL tool is new for me. I worked extensively on Informatica.

Coming to my question, right now I am trying to do SCD type 1. But my dimension table has a compound key. i.e more than 1 column makes a key for the table. But SCD wizard allows to select only 1 attribute as a business key. Does any one have any suggestions on how to implement SCD if the target table has compound key.

Thanks in advance for your suggestions and answers.

Venkat

View 5 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

Adding Primary Key To A Table Which Has Already A Primary Key

Aug 28, 2002

Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.

Thanks,
Jeyam

View 9 Replies View Related

Auto Incremented Integer Primary Keys Vs Varchar Primary Keys

Aug 13, 2007

Hi,

I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.

For example:

id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]

isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.

Regards
Mike

View 7 Replies View Related

SQL Server 2008 :: Change Primary Key Non-clustered To Primary Key Clustered

Feb 4, 2015

We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?

View 2 Replies View Related

4 Key Primary Key Vs 1 Key 'artificial' Primary Key

Jan 28, 2004

Hi all

I have the following table

CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)

I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id

In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified

I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.

What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?

Thanks Matt

View 5 Replies View Related

Primary Key

Aug 31, 2006

Hello all,I'm taking over a project from another developer and i've run into a bit of a problem. This developer had a bad habit of not using primary keys when designing various databases used by his programs. So now i've got approx 1000 tables all of which do not have primary keys assigned. Does anyone know of a tsql script that i can run that will loop through each table and add a primary key field?Thanks in advance?Richard M. 

View 2 Replies View Related

Primary Key

Aug 16, 2007

I have a Department Table.
Can any one tell me its Primary Key.
I have the order
AutoNumber, D + AutoNumber, Code,
Can you help me regarding this.
Because some people never like to use AutoNumber.
That's why I am confused.

View 3 Replies View Related

Primary Key

Nov 8, 2007

Hi..



I'm going to build database of university, but I have problem with primaru key,



This is the situation:

there are many faculities and each one has many departments,

each department has many courses,

each course has many sections..



The problem:

I want to make those fields in the same table and make the primary key generate from other fields,

(i.e)

I want the faculity be integer from 4 digit "Example the first faculity start with 1000 the second 2000 and so on" and the the department of each faculity will generate its value from the faculity number+interger number from 3digit "Example the department of the first faculity start with 1100 and the second on will be 1200 and so on "

the same thing will repeate for courses and sections so the sectionsID will be the primary key.



Do you know hoew this idea can be implement by SQL server 2005?

Please help me as soon as possible.

View 13 Replies View Related

Primary ID = BC

Mar 23, 2005

A column will be Primary Key. Others are B and C. I want A will contain B and C. I mean B data is X, C data is Y, A will be XY. How can i do this? Can i set in MSSQL or need ASP.NET?

View 1 Replies View Related

Primary Key

Dec 1, 1998

Heya,

I'm trying to setup a Primary Key on a SQL 6.5 database.

Is there a way to do this? When I hit advanced, it asks for me to select a field for the primary key, but it doesnt list fields to selct from, and I cant type it in.

Thanks for your help,

View 3 Replies View Related

DTS Is Not Getting The Primary Key

Jul 8, 2004

Hi All,

Using DTS i have imported the data from sybase to MS SQL server and all the data and tables were imported correctly.But the primary keys are not marked why is it like this?
This is not a one time job and this is meant to be for the customers also.I cannot ask the customers to mark the primary keys themselves. Is there a way to get the keys also.While doing DTS I have marked all the options correctly.

Please help.

View 5 Replies View Related

Primary Key

Sep 23, 2004

I am setting up some tables where I used to have an identity column as the primary key. I changed it so the primary key is not a char field length of 20.

Is there going to be a big performance hit for this? I didn't like the identity field because every time I referenced a table I had to do a join to get the name of object.

EG:

-- Old way
tbProductionLabour
ID (pk)| Descr | fkCostCode
----------------------
1 | REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabourID | Manpower | Hours
--------------------------------------------
1 | 1 | 1 | 0.15

-- New way
tbProductionLabour
Labour | fkCostCode
---------------------
REBAR | 1J

tbTemplateLabour
fkTemplateID | fkLabour | Manpower | Hours
-------------------------------------------
1 | REBAR | 1 | 0.15


This is a very basic example, but you get the idea of what I am referring to.

Any thoughts?

Mike

View 11 Replies View Related

Primary Key

Dec 3, 2004

I need to create my own primary key, how do I go about doing that?? In the database I am working in usually has a primary key that looks like this VL0008
the V is for Vendors, thats basically their number. Some of these Vendors need to be licensed and some dont, the ones that are not licensed dont get a number but I am to use that as the Primary/Index key I need to create one for those particual vendors. How can I go about doing that??? I was wanting to make it TL888 something like that.

View 7 Replies View Related

How To Get Primary Key....plz Help!!!!

Oct 17, 2005

i'm having problem to get th primary key from d database....
for your information i'm using java to get the primary key....
this is my code...
rs = stt.executeQuery("sp_columns "+table_db+";");
while(rs.next())
{
out.write(""+rs.getString("COLUMN_NAME"));
out.write(", "+rs.getString("TYPE_NAME"));
out.write(", "+rs.getString("IS_NULLABLE"));
}

rs = stt.executeQuery("sp_foreignkeys @table_name = N'table_db';");


but the problem is....
i get this error message...could anyone tell me what's the problem....
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver][SQL Server]Could not
find server 'table_db' in sysservers. Execute sp_addlinkedserver to add th
e server to sysservers.

how do i solve this problem....

thanx to anyone who can help me...... :D

View 7 Replies View Related

Primary Key

Oct 18, 2006

Please help:

I am creating a table called Bonus:
ProductHeading1
ProductHeading2 (could be null)
ProductHeading3(could be null)
Bonus
Datefrom
DateTo

.... what would be the primary key?! I know it would be DateTo and sumfing...... Since Heading2 and Heading3 could be null, they cannot be PK... and heading1 cannot be a PK because the following three DIFFERENT options could have the same heading1
Option 1) heading1 = "X" heading2 = Null heading3 = Null
Option 2) heading1 = "X" heading2 = "Y" heading3 = Null
Option 3) heading1 = "X" heading2 = "Y" heading3 = "Z"

... but I need a PK to make sure a bonus is not entered twice... I considered added an Id, but them how do I assign a id?! what would i make the id equal to???

Thanks....

View 14 Replies View Related

Is It Possible To To Have A Primary Key That...

Feb 3, 2008

Hi all,

Is it possible to have a primary key for SQL or Oracle or jet to have an alpanumeric beginning?

for example
1st District as a primary key

The statement is:
SELECT itemid FROM MASecurity WHERE userid=%d

Thanks,
Jj :)

View 14 Replies View Related

Name Of Primary Key

Jan 27, 2004

What 's the way to know
the name of the column that is
the primary key of a table

View 3 Replies View Related

Primary Key

Mar 11, 2004

In a recent course on database programming using Microsoft Access 2002. I noticed that the text entitiled New Perspectives Microsoft Access 2002 stated that a primary key could only be used once per table. But If I am not mistaken could one use the select key to select more than one primary key within a table.

View 8 Replies View Related

Primary Key

Apr 14, 2008

Hi guys,

Is there a method in sql server 2005 to format the primary key so it can be alphanumberic?

Thanks.

View 2 Replies View Related

Primary Key

May 5, 2008

Violation of PRIMARY KEY constraint 'PK_Dunning_TBL'. Cannot insert duplicate key in object 'Dunning_TBL'.
The statement has been terminated.

(0 row(s) affected)
Msg 2627, Level 14, State 1, Procedure GenerateFiles_FST_SP, Line 220
Violation of PRIMARY KEY constraint 'PK_Exceptions_TBL'. Cannot insert duplicate key in object 'Exceptions_TBL'.
The statement has been terminated.

i got this error how can i resolve this?

View 4 Replies View Related

Last Name-First Name Primary Key?

May 23, 2008

Hi,

I am designing a database for containing the info for the employees in the institution. My dilemma:
-Should I use an sql autonumber primary key?
or
-should I merge lastName+FirstName+middleName into one field and use this string as the primary key?

thank you

View 6 Replies View Related

Primary Key?

Jun 17, 2008

I'm new to these forums, and I'm not a database developer, per se, so please forgive me if I make any newb'ish comments.

I have a lookup table called tblCars, that has two columns, cars_id and cars_title. Typically what I do with tables like this is I make cars_id an autonumber, and cars_title the primary key.

The cars_title would contain unique data such as Ford, Chevy, Toyota, etc, which is why I like to make it the primary key (ie - guarantee that it remains unique and no duplicates are ever placed in it).

I would then create an index on cars_id so that I could use it in foreign key constraints.

However, I'm being told by a number of people that it is incorrect to make cars_title the primary key, and that the autonumber field should be the primary key. Yet I am having trouble arriving at a real good reason as to why this is the correct way to do it. I like the warm-fuzzies that I get knowing that no one can accidentally insert a duplicate car title into tblCars because of the primary key constraint.

Thanks in advance for any thoughts or insights on this.

View 6 Replies View Related

Primary Key

Feb 2, 2006

I've noticed that some of my tables have primary keys that are not referenced by a foreign key in another table, is this indicative of bad design?

Jill

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved