Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
knight07
Starting Member
17 Posts |
 Posted - 2008-07-22 : 04:32:40
|
| Hello all..I'm trying to create a unique constraint for a table in an SQL database. the table name is tblSchools and it has the following fields: schoolID(PK), schoolName(nchar(30)), schoolLevelID(FK), eduAreaID(FK), genderID(FK).The unique constraint is supposed to apply on the schoolName field so that a school name is not doubled. This is the statement"Create UNIQUE INDEX UQ_tblSchools ON tblSchools(schoolName)The error I'm getting is the following:"The Create Unique Index statement terminated because a duplicate key was found for the object name 'dbo.tblSchools' and the index Name 'UQ_tblSchools'Before trying to create this constraint, I had some duplicate school names, but I deleted them.Any idea how can i get around this?Thanx |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2008-07-22 : 04:35:49
|
| the error suggests you still have duplicate data existing. run this query and see if duplicate still existsSELECT * FROM dbo.tblSchools WHERE schoolName IN(SELECT schoolName FROM dbo.tblSchools GROUP BY schoolName HAVING COUNT(*) >1) |
 |
|
|
knight07
Starting Member
17 Posts |
Posted - 2008-07-22 : 05:07:20
|
| Thanx alot visakh16That did work great. |
|
|
|
|
|
|
Creating a unique constraint problem