Adding unique constraint.

Question

Adding unique constraint.

deepthi818

SSC-Addicted

Points: 437
More actions
April 29, 2010 at 11:02 am

#140055

I was asked in an interview today:
How can you implement an unique constraint on a column of a table in a database. The table already has data in it. Also the data in that column has multiple values. You are not allowed to make any design changes to the database like doing further normalisation.
Also say there are 10,000 rows in the table. and out of which only 8,000 have unique values in the column. How can you filter out multiple entries from unique entries.
Please let me know the answers for these questions.
Thanks

Viewing 12 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323518 More actions · Answer 1

it depends on the unique constraint.

instead of trying to do it on just one column, you should have asked if it can be expanded to cover more than just a single column, as the combination of the data in two cols might be unique. Analyzing the data is important.

assuming you cannot,a dn the constraint must cover just a single column of data, you'd have no choice but to delete duplicates in the data;

some data would have to be deleted(or updated to new, unque values) in order to satisfy a unique constraint;you can only have one NULL , so you cannot update all the duplicates to NULL.

if the interviewer told you you cannot delete data, then you'd have to say the constraint cannot be added.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 2

It has to be unique to add a unique constraint. So you either modify the data to make it unique, or you modify the structure, adding, for example, an identity column that you can populate with unique values across the table and then make a unique constraint based on both columns. But that's a very poor substitute since it would still allow duplicate values in the original column.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

deepthi818 SSC-Addicted Points: 437 More actions · Answer 3

Thanks for replying ... Thinking without the constraint of making much design changes what are the options i can explore.

Would indexed views be a good choice, ... why or why not?

This wasnt asked in the interview... but i am just exploring options ..

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 4

I don't think so, no. The problem is, I'm having a hard time understanding the issue. If you want unique data.... you have to make the data unique. You can't really have unique data that... isn't.

You're options come down to: 1) Manipulate the existing data to remove duplicates, 2) Manipulate the existing data to modify duplicates to make them unique, 3) Manipulate the structure to allow for some method of uniquely identifying the record, but that won't uniquely identify the data

To perform this operation properly, you really should do 1 or 2.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 5

Ditto to what Grant said.

I'm curious. What answer did you give in the interview?

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

deepthi818 SSC-Addicted Points: 437 More actions · Answer 6

Thanks for replying. I did not answer the question as i was confused as to what exactly he wanted.

I asked if i could modify the data... Or Change the table structure... but he said such things were not allowed. :unsure:

Cory E. SSCertifiable Points: 7076 More actions · Answer 7

Perhaps I do not understand fully the situation, but, you can make no changes, but must add the constraint...

WITH NOCHECK ?

-- Cory

Brandie Tarvin SSC Guru Points: 173068 More actions · Answer 8

Cory,

Who told you that you could be logical about the questions answered on the forum???

@=)

Honestly, that possibility didn't occur to me. Retaining old data but forcing new data to be unique. DOH.

Brandie Tarvin, MCITP Database AdministratorLiveJournal Blog: http://brandietarvin.livejournal.com/[/url]On LinkedIn!, Google+, and Twitter.Freelance Writer: ShadowrunLatchkeys: Nevermore, Latchkeys: The Bootleg War, and Latchkeys: Roscoes in the Night are now available on Nook and Kindle.

Cory E. SSCertifiable Points: 7076 More actions · Answer 9

I certainly would not WANT to do it that way, however, it seems to fit the rules. being it was an interview, I would not expect they run into the situation, just want to see if you could come up with an answer.

Had the OP not posted it was for an interview, I doubt I would have come up with that solution either.

-- Cory

Whisper9999 Hall of Fame Points: 3012 More actions · Answer 10

You know what's funny is that sometimes in interviews, they will ask q's that they don't themselves know the answer to. They're hoping that someone will solve a thorny problem for them. This kind of sounds like what they're doing here - looking for a silver bullet to solve a design problem.

Grant Fritchey SSC Guru Points: 398886 More actions · Answer 11

Cory E. (4/30/2010)
Perhaps I do not understand fully the situation, but, you can make no changes, but must add the constraint...
WITH NOCHECK ?

Excellent notion. That may very well be what they were driving at. Great catch.

That said, it's a stupid approach, but it looks like you'd get the job.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning