How to create PK in duplicate column?

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.

you can't...how would the foreign key know where to link to....row 1 or row 2 or row 3 or all of them?

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||Primary Keys are by definition "unique". You cannot have duplicates.

From your example, a index on fname is what you want.|||

Thanks,

So the question becomes "How to delete duplicate records"?

|||

fastest way would be (change realtable to your table name)

select distinct * into backuptable

from realtable

truncate realtable

insert into realtable

select * from backuptable

drop table backuptable

Denis the SQL Menace

http://sqlservercode.blogspot.com/

|||

Hi,

the results are same when using select distinct * from table1

select distinct * from table1

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

|||

The answer i gave you won't work because of the id column and the rest is not unique either

what the code below does is get the min id per fname and dumps it into a temp table

then we delete from the real table everything that is not in the temp table

I used min, you can use max

select min(id),fname into #temp
from YourTable
group by fname

delete YourTable
where not exists (select * from #temp where YourTable.id = #temp.id)

Denis the SQL Menace
http://sqlservercode.blogspot.com/

|||

you are only allowed one PK per table! you can have multiple columns as a primary key however.

sql server2000 wrote:

Hi,

How to create PK in duplicate column?

for example:

id fname lname age departments

1 aaa aaa NULL NULL
3 aaa bbb NULL NULL
7 aaa b NULL NULL

...

I want to create PK on fname column.