adding UNIQUE Constraint to existing column

Hello,
I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
duplicate key using WITH NOCHECK in SQL Server 2000.
Here is my SQL syntax:
ALTER TABLE user_email WITH NOCHECK
ADD CONSTRAINT unq_user_email_email UNIQUE (email)
Query Analyzer keeps giving me the error message that duplicate key was foun
d:
Server: Msg 1505, Level 16, State 1, Line 1
CREATE UNIQUE INDEX terminated because a duplicate key was found for index
ID 4. Most significant primary key is 'user1@.abc.com'.
Server: Msg 1750, Level 16, State 1, Line 1
Could not create constraint. See previous errors.
The statement has been terminated.
I learned from Books Online that "WITH NOCHECK" allows to add an unverified
constraint and prevents the validation against existing rows.
I tried using SQLServer Enterprise Manager, Manage Indexes and got the same
error message even though that I had checked the "Ignore Duplicate Values"
checkbox.
Can someone please tell me how i can add a UNIQUE CONSTRAINT without
removing the duplicate keys.
Thank you so much for your help!
--
MitraSQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
index does not allow duplicates, under no circumstances.
The following text is also part of the BOL article:
"The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
and UNIQUE constraints."
HTH,
Gert-Jan
mitra wrote:
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
> Query Analyzer keeps giving me the error message that duplicate key was fo
und:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
> I learned from Books Online that "WITH NOCHECK" allows to add an unverifie
d
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the sam
e
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
Thankfully you can't. From BOL:
"IGNORE_DUP_KEY
Controls what happens when an attempt is made to insert a duplicate key
value into a column that is part of a unique clustered index. If
IGNORE_DUP_KEY was specified for the index and an INSERT statement that
creates a duplicate key is executed, SQL Server issues a warning and ignores
the duplicate row."
When it says ignores the duplicate row, it means it doesn't insert it.
If you want to do what you are suggesting you will need to build a trigger.
Then just join to the parent table to see if the values already exist (make
sure to take care of the case where duplicate values are inserted via the
INSERT statement. If you want more information about how to do this, I
could help for out (as could others here :).
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"mitra" <mitra@.discussions.microsoft.com> wrote in message
news:3BB0328C-8FC1-40A8-A614-D8E2C0B43859@.microsoft.com...
> Hello,
> I am having trouble adding a UNIQUE CONSTRAINT to an existing column with
> duplicate key using WITH NOCHECK in SQL Server 2000.
> Here is my SQL syntax:
> ALTER TABLE user_email WITH NOCHECK
> ADD CONSTRAINT unq_user_email_email UNIQUE (email)
>
> Query Analyzer keeps giving me the error message that duplicate key was
> found:
> Server: Msg 1505, Level 16, State 1, Line 1
> CREATE UNIQUE INDEX terminated because a duplicate key was found for index
> ID 4. Most significant primary key is 'user1@.abc.com'.
> Server: Msg 1750, Level 16, State 1, Line 1
> Could not create constraint. See previous errors.
> The statement has been terminated.
>
> I learned from Books Online that "WITH NOCHECK" allows to add an
> unverified
> constraint and prevents the validation against existing rows.
> I tried using SQLServer Enterprise Manager, Manage Indexes and got the
> same
> error message even though that I had checked the "Ignore Duplicate Values"
> checkbox.
> Can someone please tell me how i can add a UNIQUE CONSTRAINT without
> removing the duplicate keys.
> Thank you so much for your help!
> --
> Mitra|||On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:

> SQL-Server uses a unique index to enforce a UNIQUE constraint. A unique
> index does not allow duplicates, under no circumstances.
> The following text is also part of the BOL article:
> "The WITH CHECK and WITH NOCHECK clauses cannot be used for PRIMARY KEY
> and UNIQUE constraints."
I guess to achieve the effect you want, you could use a trigger:
create table user_email (email varchar(20) not null)
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
insert into user_email values ('rpresser@.nowhere.com')
go
select * from user_email
go
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT
AS
IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
inserted.email)
BEGIN
RAISERROR ('Email must be unique',16,1)
ROLLBACK TRANSACTION
END
go
insert into user_email values ('rpresser@.nowhere.com')
Results in this output:
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
email
--
rpresser@.imtek.com
rpresser@.imtek.com
rpresser@.imtek.com
(3 row(s) affected)
Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
Email must be unique|||Not quite. Two problems. First, the trigger fires after the insert. No
matter what you insert it will fail because whatever is in the inserted
table will already be in the table. Second, you are forgetting the case
where the user inserts duplicates in the insert statement:
insert into user_email
select 'bob@.bob.com'
union
select 'bob@.bob.com'
Here you have to consider the uniqueness of the values in the inserted
table.
Using his table, this code will work:
CREATE TRIGGER user_email_add
ON user_email
FOR INSERT, UPDATE
AS
IF EXISTS ( select user_email.email
FROM inserted
join user_email
on user_email.email = inserted.email
group by user_email.email
having count(*) > 1 )
begin
RAISERROR ('Email must be unique.',16,1)
ROLLBACK TRANSACTION
return
end
go
Note that I changed it to an UPDATE trigger also, since otherwise:
UPDATE user_email
SET email = 'fred@.server.com'
would work just fine. I would still suggest against this approach. Clean
up the data and life will be peachy :)
----
Louis Davidson - drsql@.hotmail.com
SQL Server MVP
Compass Technology Management - www.compass.net
Pro SQL Server 2000 Database Design -
http://www.apress.com/book/bookDisplay.html?bID=266
Note: Please reply to the newsgroups only unless you are interested in
consulting services. All other replies may be ignored :)
"Ross Presser" <rpresser@.imtek.com> wrote in message
news:l5jr9dfyygw7$.dlg@.rpresser.invalid...
> On Thu, 03 Feb 2005 21:18:09 +0100, Gert-Jan Strik wrote:
>
> I guess to achieve the effect you want, you could use a trigger:
> create table user_email (email varchar(20) not null)
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> insert into user_email values ('rpresser@.nowhere.com')
> go
> select * from user_email
> go
> CREATE TRIGGER user_email_add
> ON user_email
> FOR INSERT
> AS
> IF EXISTS (select * FROM user_email U, inserted WHERE U.email =
> inserted.email)
> BEGIN
> RAISERROR ('Email must be unique',16,1)
> ROLLBACK TRANSACTION
> END
> go
> insert into user_email values ('rpresser@.nowhere.com')
>
> Results in this output:
> (1 row(s) affected)
>
> (1 row(s) affected)
>
> (1 row(s) affected)
> email
> --
> rpresser@.imtek.com
> rpresser@.imtek.com
> rpresser@.imtek.com
> (3 row(s) affected)
> Server: Msg 50000, Level 16, State 1, Procedure user_email_add, Line 7
> Email must be unique

Adding SDF data into a Listview - C#

Hello everyone, I finally got the SDF file on my pocket pc, and now am having trouble loading the data into the listview. I am not sure where I am going wrong, but the code is listed below. Any ideas? Thanks so much, everyone.

private void LoadList(string szSQL)

{

// Clear out anything that might already be in the list

CollectionList.Items.Clear();

// save off the SQL Statement

m_szCurrFilter = szSQL;

// Throw Data into the list

SqlCeCommand sqlLoadEvents = m_cConn.CreateCommand();

sqlLoadEvents.CommandText = szSQL;

SqlCeDataReader rReader = sqlLoadEvents.ExecuteReader();

// roll through the reader and put items into the list

while ( rReader.Read() )

{

ListViewItem lvItem = new ListViewItem(rReader.GetValue(rReader.GetOrdinal("ca_id")).ToString()); //Nmbr

// Note: Since a "tag" is not supported by the compact framework, we'll use the "ImageIndex"

// property to hold the movie ID, this way we can find it later when the user clicks

// on an item

lvItem.ImageIndex = (int)rReader.GetValue(rReader.GetOrdinal("ca_id"));

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_titel")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_titel")).ToString()); // Title

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_alter")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_alter")).ToString()); // Rating

lvItem.SubItems.Add(rReader.IsDBNull(rReader.GetOrdinal("ca_sex")) ? "" : rReader.GetValue(rReader.GetOrdinal("ca_sex")).ToString());// Gender

CollectionList.Items.Add( lvItem );

}

}

I think I have narrowed it (somewhat) down:

The following item is giving me problems, I think.

LoadList("SELECT * FROM fragenkatalog");

-

fragenkatalog is the table in the database.

Thanks again.

|||

those calls to getOrdinal("columnName") combined with a SELECT * command are going to make your code as slow as possible.

the goal you have of storing the ID in the ImageIndex is not going to work, but what you are trying to do is what I call "poor-man's databinding" and I understand why you are doing it. In controls like datagrids and listviews, if you want ultimate performance (meaning, avoiding binding), you can create an extra column in your ListView, set it's width to zero, and in this column, store the ID you need on the row.

Here is a sample of filling a listview with the results of a complex database query. Note that we explicitly list the columns needed in the SQL, we refer to the ordinals by offset, close/disposee the data reader, etc. In this example, there are 2 columns with a width of zero that store GUIDs that the application needs for each row selected - poor mans databinding.

private void _populateTodaysTasks()
{
string sql = "SELECT a.ScheduledStartDate, b.Name, a.Name, c.Name, a.ID, c.ID, a.ActualStartDate, a.ActualEndDate, a.Synchronized " +
"FROM Job a, Customer b, Form c, Site d " +
"WHERE a.TechnicianID = '" + Globals.GetInstance().TechnicianID + "' " +
"AND a.SiteID = d.ID AND d.CustomerID = b.ID " +
"AND a.FormID = c.ID " +
"AND a.Synchronized = 0 " +
"ORDER BY a.ScheduledStartDate";

SqlCeDataReader dtr = DatabaseManager.GetInstance().ExecuteQuery(sql);

if ( null != dtr )
{
while ( dtr.Read() )
{
ListViewItem lvi = new ListViewItem(dtr.GetString(0));
lvi.SubItems.Add(dtr.GetString(1).Trim());
lvi.SubItems.Add(dtr.GetString(2).Trim());
lvi.SubItems.Add(dtr.GetString(3).Trim());
lvi.SubItems.Add(dtr.GetGuid(4).ToString()); // jobID
lvi.SubItems.Add(dtr.GetGuid(5).ToString()); // formID
string start = dtr.GetString(6).Trim();
string end = dtr.GetString(7).Trim();
bool sent = dtr.GetBoolean(8);

// removed some code that uses start and end to determine if a task is past due

lvwTasks.Items.Add(lvi);

}
dtr.Close();

dtr.Dispose();

}

}

Darren

|||

Thank you Darren, I appreciate your help.

I will try this out later on and let you know how I did. :)

I really appreciate it!

Martina

|||

Well, I'm confused.

I have re-thought everything again, gone through the code, and have simplified it somewhat (I think). But I am still not getting the list of items in the listview box. In my sdf file, I have 1 Table (fragenkatalog) and within it, I have 5 columns (id (int), title (nvarchar (150)), picture (nvarchar (100)), age (int), and gender (nvarchar (1)) ) -- but I am only listing the first 3 in the listbox.

The way the code is now, I am not getting any error messages when I debug.

I am including the code for the application.... I'm at such a loss, and I've been using the ".NET Compact Framework KickStart" book (Rubin, Yates) and have not been having much luck with finding my answer there.

If someone could give me a helping hand, I would be very appreciative.

Thanks again,

Martina

using System;

using System.Drawing;

using System.Collections;

using System.ComponentModel;

using System.Windows.Forms;

using System.IO;

using System.Text;

using System.Data;

using System.Data.SqlServerCe;

using System.Data.Common;

namespace PedBoneView

{

/// <summary>

/// This is the default form.

/// <summary>

public class Form1 : System.Windows.Forms.Form

{

private System.Windows.Forms.ColumnHeader columnHeader1;

private System.Windows.Forms.ColumnHeader columnHeader2;

private System.Windows.Forms.ColumnHeader columnHeader3;

private System.Windows.Forms.MenuItem New;

private System.Windows.Forms.ListView lVListe;

private System.Windows.Forms.ColumnHeader columnHeader4;

private System.Windows.Forms.ColumnHeader columnHeader5;

private System.Windows.Forms.ColumnHeader columnHeader6;

private System.Windows.Forms.MainMenu mainMenu1;

SqlCeConnection conn = null;

private System.Windows.Forms.ContextMenu contextMenu1;

private System.Windows.Forms.MenuItem menuItem2;

private System.Windows.Forms.MenuItem menuItem3;

SqlCeCommand cmd = null;

public Form1()

{

//

// Required for Windows Form Designer support

//

InitializeComponent();

//

// TODO: Add any constructor code after InitializeComponent call

//

}

/// <summary>

/// Clean up any resources being used.

/// </summary>

protected override void Dispose( bool disposing )

{

base.Dispose( disposing );

}

static void Main()

{

Application.Run(new Form1());

}

private void Form1_Load(object sender, System.EventArgs e)

{

try

{

if (!File.Exists ("//My Documents//pedbone.sdf")) // If the database doesn't exists, create it

{

SqlCeEngine engine = new SqlCeEngine ("Data Source = //My Documents//pedbone.sdf");

engine.CreateDatabase ();

conn = new SqlCeConnection ("Data Source = //My Documents//pedbone.sdf");

conn.Open();

cmd = conn.CreateCommand();

cmd.CommandText = "CREATE TABLE fragenkatalog(ca_id int PRIMARY KEY IDENTITY(1,1), ca_titel nvarchar(150), ca_bildurl1 nvarchar(100), ca_alter int, ca_sex nvarchar(1))";

cmd.ExecuteNonQuery();

conn.Close();

}

else // else initialize the connection

{

conn = new SqlCeConnection ("Data Source = //My Documents//pedbone.sdf");

cmd = conn.CreateCommand();

UpdateLView();

}

}

catch (SqlCeException ex)

{

}

}

//New entry

private void New_Click(object sender, System.EventArgs e)

{

New newpbdata = new New(this);

newpbdata.Show();

}

public void UpdateLView() //This function updates the main listview

{

// lVListe.Items.Clear();

conn.Open();

cmd.CommandText = "SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100) FROM fragenkatalog";

SqlCeDataReader rdr = cmd.ExecuteReader();

if ( null != rdr )

while(rdr.Read())

{

ListViewItem lvi = new ListViewItem(rdr.GetString(0));

lvi.SubItems.Add(rdr.GetString(1));

lvi.SubItems.Add(rdr.GetString(2));

lvi.SubItems.Add(rdr.GetString(3));

lVListe.Items.Add(lvi);

}

rdr.Close();

rdr.Dispose();

}

}

}

|||

While you do create a database and a table, you have not inserted any actual data into the table before you attempt to read from the table and populate the listview.

Try inserting some data into the database table.

-ds

|||

I already have data in the database table (about 750 rows).

Is that not possible to do?

Technically, I just want to show the data that's in the table, not edit, add, or remove any of the data.

Thanks again.... :)

|||

How is the ListView itself configured? Did you use the VS2005 designer to add columns and set the view to Details with/without checkboxes or are you doing this in code?

Be sure you have created ListViewColumns that match what you are trying to display in the ListView and that you have set the Default View to Details.

Post the portion of the InitializeComponent method that initializes the listview if you want me to review that.

This should not be so difficult - there is something small and easy you have wrong and we just need to find it.

Darren

|||

Darren,

Thank you so much for your help. I'm really feeling like an idiot at the moment, simply because I cannot find the problem myself. I was getting worried that it might be my sdf file....

I'm using code to do this. I am using MS Development Enviroment 2003...

I hope I've provided the code you need.

Thank you very much again...

Martina

--

private void InitializeComponent()

{

this.mainMenu1 = new System.Windows.Forms.MainMenu();

this.New = new System.Windows.Forms.MenuItem();

this.lVListe = new System.Windows.Forms.ListView();

this.columnHeader1 = new System.Windows.Forms.ColumnHeader();

this.columnHeader2 = new System.Windows.Forms.ColumnHeader();

this.columnHeader3 = new System.Windows.Forms.ColumnHeader();

this.columnHeader4 = new System.Windows.Forms.ColumnHeader();

this.columnHeader5 = new System.Windows.Forms.ColumnHeader();

//

//

// lVListe

//

this.lVListe.Columns.Add(this.columnHeader1);

this.lVListe.Columns.Add(this.columnHeader2);

this.lVListe.Columns.Add(this.columnHeader3);

this.lVListe.Columns.Add(this.columnHeader4);

this.lVListe.Columns.Add(this.columnHeader5);

this.lVListe.ContextMenu = this.contextMenu1;

this.lVListe.FullRowSelect = true;

this.lVListe.Size = new System.Drawing.Size(240, 216);

this.lVListe.View = System.Windows.Forms.View.Details;

// columnHeader1

//

this.columnHeader1.Text = "ID";

this.columnHeader1.Width = 30;

//

// columnHeader2

//

this.columnHeader2.Text = "Title";

this.columnHeader2.Width = 80;

//

// columnHeader3

//

this.columnHeader3.Text = "Image";

this.columnHeader3.Width = 60;

//

// columnHeader4

//

this.columnHeader4.Text = "Age";

this.columnHeader4.Width = 35;

//

// columnHeader5

//

this.columnHeader5.Text = "Sex";

this.columnHeader5.Width = 35;

//

// Form1

//

this.Controls.Add(this.lVListe);

this.Menu = this.mainMenu1;

this.Text = "PedBone Pocket";

this.Load += new System.EventHandler(this.Form1_Load);

|||

I think I see the problem.

The ListView object you define has 5 columns:

ID Title Image Age Sex

and the ListView items you are trying to add to the ListView only have 3 columns resulting from this select statement

SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100) FROM fragenkatalog

the number of SubItems in a ListViewItem must be one less than the number of columns defined in the ListView itself. So, in other words, you create a new ListViewItem and give it the name 'ID'. If there are 5 total columns defined in the ListView, you need to add 4 more subitems to this ListViewItem so that 'ID' plus the other 4 subitems constitute a total of 5 items which match to 5 columns.

Darren

|||

Hmmmmmmm.....

That didn't seem to work... Or have I missed something again? I have triple-checked upper- and lower case, so that's not it. I checked the nvarchar values, and those seem to be okay too. I did upper case ID and lower case id, ca_id too.

I'm still not getting any errors, but also nothing in the ListView.

Here's the updated code....

Thank you again, Darren.

public void UpdateLView() //This function updates the main listview

{

lVListe.Items.Clear();

conn.Open();

cmd.CommandText = "SELECT ca_id int, ca_titel nvarchar(150), ca_bildurl1 nvarchar(100), ca_alter int, ca_sex nvarchar(1) FROM fragenkatalog";

SqlCeDataReader rdr = cmd.ExecuteReader();

if ( null != rdr )

while(rdr.Read())

{

ListViewItem id = new ListViewItem(rdr.GetString(0)); //this should be the id #

id.SubItems.Add(rdr.GetString(1)); //title

id.SubItems.Add(rdr.GetString(2));//picture

id.SubItems.Add(rdr.GetString(3));//age

id.SubItems.Add(rdr.GetString(4));//gender

lVListe.Items.Add(id);

}

rdr.Close();

rdr.Dispose();

}

|||

Just email me your SDF file and I will send you the working code - newsgroups are awful for this sort of thing and it would just be faster for me to fix it and send it to you.

Do you want all 5 columns in the listview (or did you only want the 3 you originally had in the sql select statement?)

Google me and contact me through my BLOG.

Darren Shaffer

|||

I did receive your SDF file and I have sent you back a working solution with a note explaining the necessary changes. Check your email.

Darren

Adding rows together

Hello,
I'm having trouble finding a site with basic introduction to SQL Reporting
Services. I'm using the VS.nET reporting services IDE to build some reports
and I have a few questions.
How do I SUM two "report items" on my report? Like textbox12.value +
textbox13.value. I know how to do it with the "Fields" on my report but not
actualy report items.
Thanks
nTry using the "=ReportItems!Textbox12.value + ReportItems!Textbox13.value".
"nigel" wrote:
> Hello,
> I'm having trouble finding a site with basic introduction to SQL Reporting
> Services. I'm using the VS.nET reporting services IDE to build some reports
> and I have a few questions.
> How do I SUM two "report items" on my report? Like textbox12.value +
> textbox13.value. I know how to do it with the "Fields" on my report but not
> actualy report items.
> Thanks
> n

Adding or subtracting

I'm in trouble. I have a table with some fields, one of them is an amount of money.
One field indicates whether the amount is negetive or positive. This fields has only two possible values, 'N' and 'R'. 'N' means the amount is negative and 'R' means the amount is positive.

The problem I have is that I have to sum these amounts to give a total for each customer and report them as

customer1 total amount1
customer2 total amount2

I don't know how to make a query to bring these results. I know that sum would bring me the total amount for each customer but I don't know how to specify which fields are supposed to be negative so that it subtracts them instead of adding them.

By the way I'm not allowed to change the structure of the table.

Could you help me try to create this query?you need to use a CASE stmt. check out BOL for some samples.

hth|||Could you give me an example through this thread please. I have no access to other internet pages. I can only access this one.

I tried something like this but it didn't work out

select r.numpolso, r.numofic,
case r.tdocumen
when 'N' then sum (r.prmtotrb*'-1')
when 'R' then sum (r.prmtotrb)
end as r.prmtotrb
from trecibos r
where r.numpolso = '1010' and r.ramo = '90' and r.subramo = '14' and r.numofic = '001'
group by r.numofic, r.numpolso, r.prmtotrb

I got a syntax error msg

could you give me an example or another way of doing this?

thanks in advance