Author |
Topic |
Abid
110 Posts |
Posted - 2011-05-23 : 00:42:10
|
hi, lets suppose i have a table in which there are 8 attritubes out of which 2 can be used as a primary key, e.g. St_ID, and National_ID_No. So if i make St_ID as a primary key, so what i do with this other one. do i let it go like other non key attributes, or something else? |
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-05-23 : 01:34:33
|
there can be only one primary key on a table.so,if you want to make st_id as primary key and want uniqueness in both the columns create unique constraint on national_id column....so that both of your columns will have unique data... |
|
|
Abid
110 Posts |
Posted - 2011-05-23 : 03:05:01
|
hi ahmed bhai, what you mean to say to create unique constrain on natioanl_id column? |
|
|
ahmeds08
Aged Yak Warrior
737 Posts |
Posted - 2011-05-23 : 05:07:21
|
if you make std_id as primary key,you dont have any other option left on national_id column if you have have both of them in same table...if you are having seperate data related to National then you can split it into one more table and make the national_id as primary key and then you can refer it as foreign key in the other table containg std_id column. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-23 : 06:15:28
|
>> of which 2 can be used as a primary keyThey are called candidate keys.One becomes a primary key the other an alternate key with a unique constraint (index) on it.I would guess that ST_ID should be the PK as it sounds like Nation_ID might not always be known on insert - you can allow multiple nulls in this column by filtering the index.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
jfarrugia
Yak Posting Veteran
55 Posts |
Posted - 2011-05-23 : 07:56:19
|
Another option would be selecting both as p.ks.. if both your IDs together are unique, you can select both as p.ks.. they're called composite keys which are keys with more than one field. However be cautious.. you can never have two identical composite keys .Where software development knowledge meets the reader |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-23 : 08:09:55
|
quote: Originally posted by jfarrugia Another option would be selecting both as p.ks.. if both your IDs together are unique, you can select both as p.ks.. they're called composite keys which are keys with more than one field. However be cautious.. you can never have two identical composite keys .Where software development knowledge meets the reader
Not the same thing. Having two candidate key columns is not the same as the combination being unique.You need to have a unique constraint on each individually.You could make the combination the PK and also have a unique constraint on each individually - but that would make the table generally less efficient and take up more room than making one a PK.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Abid
110 Posts |
Posted - 2011-05-23 : 10:19:42
|
nigelrivett, what you say that having composite key in a table is a good programming practice, or we should go with having single primary key? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-23 : 10:29:35
|
If the two columns could be a PK on there own then you shouldn't make them a composite key. Pick just one of them to be the PK.The other gets a unique index.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Abid
110 Posts |
Posted - 2011-05-23 : 12:41:10
|
quote: Originally posted by nigelrivett If the two columns could be a PK on there own then you shouldn't make them a composite key. Pick just one of them to be the PK.The other gets a unique index.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
you said that pick just one of them to be the PK. The other gets a unique index. do you mean to say that i make one of them as a PK, and let the other one just like other attributes, just like non key attribute? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-23 : 12:44:23
|
No - add a unique index to the other column. It is a unique columns so shuold have a constraint to enforce it.create table xxxx(ST_ID int primary key ,National_ID int ,,,,)gocreate unique index ix_xxxx_01 on xxxx (National_ID)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Abid
110 Posts |
Posted - 2011-05-24 : 02:33:41
|
quote: Originally posted by nigelrivett No - add a unique index to the other column. It is a unique columns so shuold have a constraint to enforce it.create table xxxx(ST_ID int primary key ,National_ID int ,,,,)gocreate unique index ix_xxxx_01 on xxxx (National_ID)==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
hi, I'm using the SQL sERVER 2005 standard edition. and i don't work in queries, infact i dont know how to manipulate different type of queries. so please assist me in sql server. I didnt get still the proper meaning of your reply? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-24 : 02:58:47
|
Do you know what an index is and a primary key and how they are implemented?You need to get used to sql if you want to do anything with a database.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Abid
110 Posts |
Posted - 2011-05-28 : 13:06:07
|
quote: Originally posted by nigelrivett Do you know what an index is and a primary key and how they are implemented?You need to get used to sql if you want to do anything with a database.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy.
hi, yes sir, i know the primary key, and know little bit about index too.and you said sir that i need to get used to sql? so i become used to with it? |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-05-28 : 13:59:42
|
Work in query windowsCreate table mytbl(ST_ID varchar(20) not null ,National_ID varchar(20) not null unique ,...)goalter table mytbl add constraint pk_mytbl primary key (ST_ID)gocreate unique index ix_mytbl_01 on mytbl (National_ID)goMost things you doin the gui you can script to see the sql generated.Save xcripts in a source control system so you can record changes and make all database access via stored procedures.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
Abid
110 Posts |
Posted - 2013-04-16 : 09:21:33
|
Hi Guys.I think that this time i would go to use St_ID as PK and use another table, where National_ID will be PK of that particular table. Because after normalizing, i found that these 2 can't reside in single table, its violating the Normalization rules.I'm extremely thankful to all of you for your sincere devotion. |
|
|
|