Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL 23505

Posted on 2012-09-02
5
Medium Priority
?
624 Views
Last Modified: 2013-08-07
I have an existing table, and the employee_id columns contain 0. So I decided to add a unique constraint to employee_id, but I got error SQL state: 23505. To solve this problem,  what should I do?
0
Comment
Question by:JavaTears
  • 2
4 Comments
 
LVL 38

Accepted Solution

by:
Gerwin Jansen, EE MVE earned 800 total points
ID: 38361294
How nice of you to select 3 database types, please let us know which one this is about so we can help you better :)

But to help fix your issue, is there a difference between:

select count (employee_id) from your_table;

and

select distinct (employee_id) from your_table;

In case there is a difference then the constraint cannot be applied because you do not have unique values in your_table's employee_id field.
0
 
LVL 32

Assisted Solution

by:awking00
awking00 earned 800 total points
ID: 38363916
>>I have an existing table, and the employee_id columns contain 0.<<
The error is due to the fact that your employee_id column does not contain unique values (e.g. there are multiple 0's in the column), which are necessary to create a unique constraint. You would need to update the column so that the values are unique before adding the constraint. Which dbms are you actually using since that would determine how the update would need to be applied?
0
 
LVL 38

Expert Comment

by:Gerwin Jansen, EE MVE
ID: 38364150
@awking00 - I'm also suggesting that employee_id is not having unique values :D
0
 
LVL 23

Expert Comment

by:Steve Wales
ID: 39388564
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
Steps to create a PostgreSQL RDS instance in the Amazon cloud. We will cover some of the default settings and show how to connect to the instance once it is up and running.
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
Suggested Courses
Course of the Month10 days, 20 hours left to enroll

572 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question