| Author |
Topic |
|
sqlbug
Posting Yak Master
201 Posts |
 Posted - 2010-03-25 : 14:33:49
|
I was transferring some data from an old oracle table to a sql server table. First I imported the oracle table to sql server, then I used a query to map and bring data to a new table. The query wouldn't run when the primary key was on, even though I have condition to filter so duplicates can't get in.So I took off the key to get the data. After that - I tried to put the Primary key back and it wouldn't allow me to save. However, if I created an Unique index on the same columns - that would succeed.More Weird, If I run a query to return the rows for a group, It comes up with duplicate rows for most of them. One row (row 1) contains NULL values for 2 columns I used for mapping and a value in another column thats not part of the key and nullable (grp_eqp).grp_id grp_ctype grp_param grp_method grp_eqp grp_site grp_plot1317 1 43911 020 12 NULL NULL1317 1 43911 020 NULL 13 096 The 2nd row contains values in those two columns used for mapping but a null in the grp_eqp.However If I run a query that will return all the rows that have nulls in these two columns, it doesn't return any.Anybody ever had this sort of problem in sql server?Thanks, |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-25 : 14:38:04
|
| So I took off the key to get the data. After that - I tried to put the Primary key back and it wouldn't allow me to savemay be because it had NULL values already. PK cant have NULL values while unique index can have a single NULL valueIf I run a query to return the rows for a group, It comes up with duplicate rows for most of themmay be you're looking at individual columns themselves rather than combination. composite pk or unique index only ensures distinct group values not that individual column values are uniqueHowever If I run a query that will return all the rows that have nulls in these two columns, it doesn't return any.it should as per sample data, can you show query used?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2010-03-25 : 14:39:34
|
| You didn't say what the error message was that you got when you tried to create the primary key.Also, you should post the create table code for that table, so that we can see exactly how it is designed.CODO ERGO SUM |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-25 : 14:43:37
|
| I did a quick read so maybe I missed someting, but a PK doesn't allow NULLs and a Unique Index/Constraint does allow nulls as long as those combination are unique. From the data you posted it looks like that is the difference. |
|
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2010-03-25 : 16:15:30
|
| Well...the first 4 columns make the primary key and none of them had NULL values in there. I think some error happened in the sql server without a notification. I removed all data and reinserted without modifying the query and now things are back to normal.Thanks for all your feedbacks. |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
Posted - 2010-03-25 : 16:20:01
|
| Did you use NOLOCKs? |
|
|
|
sqlbug
Posting Yak Master
201 Posts |
Posted - 2010-03-25 : 18:21:08
|
| Yes I did. |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2010-03-25 : 18:50:08
|
don't  never use nolock for anything you need to be transactionally consistent |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2010-03-26 : 11:18:13
|
quote:
Originally posted by sqlbug
Yes I did.
do you know consequences of using that?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
|
|
|
Lamprey
Master Smack Fu Yak Hacker
4614 Posts |
|
|
|
Incomprehensible table/storage problem