SQL Server Performance Forum – Threads Archive

Number of rows

Hello, guys. I have one to one relationship, but in one table I have 1000 rows and in the second table – 1050. What should I do about it? Thanks,
Alex.

Strange question! What about finding out, what went wrong? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – –Frank http://www.insidesql.de</a>

It Seems pretty difficult question, bcoz we have to find the question and have to answer as well. I guess , u r thinking that both table must have same number of rows ?
is it is ur question , then check in second table on base of your primary key. and find out the reason. or Pls give more details abt ur problem.
Thnx
Sandy

I am trying to analyze a database that I didn’t create. Where is a starting point for me to find out what went wrong? <blockquote id="quote">quote:<hr height="1" noshade id="quote">Originally posted by FrankKalis Strange question! What about finding out, what went wrong? [<img src=’/community/emoticons/emotion-1.gif’ alt=’

‘ />] – –Frank http://www.insidesql.de</a> <hr height="1" noshade id="quote"></blockquote id="quote">

On a database level, I would check both tables for referential integrity stuff, check what rows are in the one table that are not in the other, check if the apps accessing the database might be buggy… It should be easy to get rid of that data, but that would probably heal only the symptoms not the cause. —
–Frank
http://www.insidesql.de

Btw, must be pretty early in the morning over there in the USA, right? —
–Frank
http://www.insidesql.de

Also, after you clean up the data, make sure the relationship is strongly enforced so you don’t have the problem going forward. You might want to put some logging in place so you can find out what’s going on. Are you still having bad data inserted? Which table is the parent in the relationship? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

As I said before tables have one-to-one relationship but number of records are different. In the table_1 primary key is a combination of two columns (a,b) and in the table_2 â€“ pk is a combination of three columns (c,d,e).
Table_2 has foreign key (a,b). I did select a, b, count(*) from table_2 group by a, b, having count(*) > 1. I found out that table b has duplication of (a,b) combination.
I checked out these duplicated records in table_2 and saw that in those records column c has different values. So, what do I do next? And if I need to delete one of the records (supposedly one of c values are wrong) what statement should I use?
Thank you.
Alex.

quote:Originally posted by derrickleggett Also, after you clean up the data, make sure the relationship is strongly enforced so you don’t have the problem going forward. You might want to put some logging in place so you can find out what’s going on. Are you still having bad data inserted? Which table is the parent in the relationship? MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.

It is a little weird to have a 1-to-1 relationship where the matching fields are not the primary keys on both sides. Other than that … Was the relationship created in Enterprise Manager after rows had been added to table_2, and with the "Check existing data on creation" NOT selected? I also wonder what happens if you do not specify cascade deletes or updates in your relationship.

Thanks Andrian for your respond. What do you mean by saying primary keys on both sides?
A primary key in one one-to-one relationship table is a foreign key in another, but the second table(a child) has his own primary key. Please, correct me if I am wrong.

quote:Originally posted by Adriaan It is a little weird to have a 1-to-1 relationship where the matching fields are not the primary keys on both sides. Other than that … Was the relationship created in Enterprise Manager after rows had been added to table_2, and with the "Check existing data on creation" NOT selected? I also wonder what happens if you do not specify cascade deletes or updates in your relationship.

you wrote
Table_2 has foreign key (a,b). I did select a, b, count(*) from table_2 group by a, b, having count(*) > 1. I found out that table b has duplication of (a,b) combination.
I checked out these duplicated records in table_2 and saw that in those records column c has different values. So, what do I do next? And if I need to delete one of the records (supposedly one of c values are wrong) what statement should I use?
Thank you.
Alex.
Alex, the next logical step is to locate documentation/developer/designer of that system and talk to him/her about it. Is a,b combination supposed to be unique in table2? If yes, how did the application insert the rows there, is this intended (say for history tracking purposes) or a bug? If it is not supposed to be unique, why are you told that there is a 1-1 relationship? Is it 1-1 relationship betweent table 1 and latest pair in table2 ("user can have only one active configuration at the time")?
Technically, it is very easy to find and fix any ‘orphan’/duplicate row issues – however before any data modifications are done, check with the people around you to make sure that this is a right solution for your particular case. without knowing what your application is supposed to do and knowing how business rules are built into it, there is no point recommending any specific action. Also , about strongly enforcing ref. integrity as someone recommended her already- be carefull and check how your indexes are built when you do this to avoid table/primary key scans as you modify the parent table. Review the nature and the amount of DML running against particular table before deciding if you want to deal with cascading deletes. simas

It sounds like the database doesnt have built-in referencial integrity. The one-to-one you talk about appears to be a business rule. There may be code in the front end system to stop any breaks to this rule, but unless the integrity is in the backend database, data can be changed that way that would break business rules.
To find out what, if anything, is defined at the table level, run this command on both of your tables: sp_help tablename Down in the list, you’ll find indexes, keys and constraints. (you can also run sp_helpconstraint but sp_help gives you other information that may help) If you have defined foreign keys, they will be here. If you have primary keys and/or unique indexes you’ll be able to see them here. Look at these. Look at the data. Look at the business requirements and then see about changing the information accordingly. Once you know this, you’ll be able to figure out a rule to determine which records to delete. If its a common one (say keep the most recent) then we can help you with the code.

Well the situation is perfectly logical. I believe that the original table had grown too wide and was divided verically into two tables. Primary Key / Foreign key relationship was maintained on the same for maintaining data integrity and since foreign key allows to insert duplicate data for the same values, multiple entries exist for the same combination in the second table. As previously suggested, I would recommend checking with the application guys if what you think is correct that there has to be only one entry in the second table for each row in the first table and if this is true then consult with them to find out which data is irrelevant. Gaurav
Moderator
Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard
The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.

Hi, First u check with ur development team for this , is it a bug ? or what ?.they can only tell u why it is happening. and when u talk about one 2 one relationship then both of the table must have same primary key columns.Primary Key and foreign key relationship never generates one 2 one relationship. and for deleting the records . u can open cursor for duplicate records and delete them.
Or
U can insert a identity column and can delete on base of that. Again i will say u , first u check with ur development team and then take any action. Thanks
Sandy

]]>