SQL Server Performance Forum – Threads Archive

composite key

Hi
I have 5-6 columns(DeptNo,FundNo,APNo,Ledger,Source) in a table for which i want it to be composite key, is this the right thing i am doing?
these columns will be repeating in most of the tables, how can i relate them by just keeping all the fields in 1 table and relate with a foreign key to other tables.
I am very bad at designing can anyone please help me?
Thanks!

I think we need more details to give you a good answer. Can you expain the business logic.

Dinesh,
The thing is all those 5 columns cannot be repeated at same time and so i thought of making all of them a composite key.
There are many tables where all these columns will be present in them. Instead of adding all these columns to each table is there a way so that i can add only 1 column as foreign key for tht.

If you have identified a natural PRIMARY KEY consisting of 5-6 columns, I would consider adding a surrogate column, namely an IDENTITY column. This might be against the theoretical rules, but saves storage space, thus speeding up performance. And will make your schema easier to maintain.
If your parent table has a composite PRIMARY KEY of 5-6 columns, you would need to add all of these columns to any child table in order to establish a relation. It won’t work with any subset of your composite PRIMARY KEY. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

If your object consist one or two out of those five – six columns in many objects and if you use composite keys then as Frank said would lead to performance issue and would you please specify a bit more information so that you can get good answer… <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis If you have identified a natural PRIMARY KEY consisting of 5-6 columns, I would consider adding a surrogate column, namely an IDENTITY column. This might be against the theoretical rules, but saves storage space, thus speeding up performance. And will make your schema easier to maintain. If your parent table has a composite PRIMARY KEY of 5-6 columns, you would need to add all of these columns to any child table in order to establish a relation. It won’t work with any subset of your composite PRIMARY KEY. – Frank Kalis Microsoft SQL Server MVP http://www.insidesql.de</a> Ich unterstÃ¼tze PASS Deutschland e.V. <a target="_blank" href=http://www.sqlpass.de>http://www.sqlpass.de</a>) <hr height="1" noshade id="quote"></blockquote id="quote"> [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] Regards. hsGoswami [email protected]

Hemanth and Frank
As you said ofcourse there may be a performance issue so I thought of having an Identity column for the fields(DeptNo,Seq,AppNo,FundNo,Ledger,Source) so that I can have that Identity column as foreign key in other tables.But my doubt is if I set my Identity column as PK then there will be a chance of duplicate records for other fields like shown below. Id DeptNo Seq ApNo FundNo Ledger Source
1 012 234 23 002 7 F
2 012 234 23 002 7 F
3 013 345 34 004 4 L Here 1st 2 rows are repeated with same values, but I dont want this to happen.In this table I cannot pull a record with Identity column, i shud do it only through remaining columns.How can I do this?
This may look silly for you but I am new to sql server so pls dont mind. Thanks for you help.

You can create a UNIQUE constraint or a UNIQUE NONCLUSTERED INDEX. That way you can avoid duplicates in non PRIMARY KEY columns. —
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Thanks a lot Frank its working fine for me.

Hey Frank
I have a doubt, i think its my fault i didnt mention you something,I want the columns to allow duplicate rows with one of them shud be different BUT not in such a way that all the coulmns have repeated value at the same time say for example Id DeptNo Seq ApNo FundNo Ledger Source
1 012 234 23 002 7 F
2 012 234 23 002 7 F
3 013 345 34 004 4 L
4 012 234 23 002 7 L Here 2nd row shud not occur but 3rd row shud occur coz the Source field is diff. I dont think UNIQUE CONSTRAINT will work in such a case. I hope you understand, may be I am bad at explaining things for you, I apologize for that.

Please try and check theirs a option for ignore duplicate values…..
Regards hsGoswami
[email protected]

Did you mean the 4th row? This row differs in only one column from the 1st and 2nd. Anyway, I think a UNIQUE constraint will do just fine here.
set nocount on
create table showme
( id int identity primary key
, deptno char(3)
, seq char(3)
, apno int
, fundno char(3)
, ledger int
, source char
constraint u_showme unique nonclustered(deptno, seq, apno, fundno, ledger, source)
)
insert into showme select ‘012’, ‘234’, 23, ‘002’, 7, ‘F’
insert into showme select ‘012’, ‘234’, 23, ‘002’, 7, ‘L’
insert into showme select ‘013’, ‘345’, 34, ‘004’, 4, ‘L’
insert into showme select ‘012’, ‘234’, 23, ‘002’, 7, ‘L’
select * from showme
drop table showme
set nocount off results in Server: Msg 2627, Level 14, State 2, Line 1
Violation of UNIQUE KEY constraint ‘u_showme’. Cannot insert duplicate key in object ‘showme’.
The statement has been terminated.
id deptno seq apno fundno ledger source
———– —— —- ———– —— ———– ——
1 012 234 23 002 7 F
2 012 234 23 002 7 L
3 013 345 34 004 4 L
Isn’t it this what you are after?
—
Frank Kalis
Microsoft SQL Server MVP
http://www.insidesql.de
Ich unterstÃ¼tze PASS Deutschland e.V. http://www.sqlpass.de)

Frank
I was trying with sample data in it and its working fine as of now.I must really thank you for the concern and help you are doing for me. Thanks for dinesh and hemanth also.

]]>