Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 How to solve this situation.

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...
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-23 : 06:15:28
>> of which 2 can be used as a primary key
They 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.
Go to Top of Page

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
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

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 ,
,,,
)
go
create 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.
Go to Top of Page

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 ,
,,,
)
go
create 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?
Go to Top of Page

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.
Go to Top of Page

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?
Go to Top of Page

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2011-05-28 : 13:59:42
Work in query windows

Create table mytbl
(
ST_ID varchar(20) not null ,
National_ID varchar(20) not null unique ,
...
)
go
alter table mytbl add constraint pk_mytbl primary key (ST_ID)
go
create unique index ix_mytbl_01 on mytbl (National_ID)
go

Most 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.
Go to Top of Page

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.
Go to Top of Page
   

- Advertisement -