Absolute SQL Beginner here
I am using SQL Server 2005 Express Edition in conjunction with asp.net 2.0
Is there any way to guarantee when INSERTing data, that all values in a given column (which is of a char data type) are unique? Or do I have to do this programatically at the asp layer?
Thanks in advance
Larry
Hello,
This probably has been addressed before but I was unable to get the search to work properly on this site.
I am needing a script/way of deleting all rows from a DB with the exception of one record left for each row that has duplicate column data. Example :
Row 1
Field1 = 12345 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Row 2
Field1 = 12345 Field2 =zzzzzz Field 3=xxxxxx Field4=yyyyyy etc.
Row3
Field1 = 12345 Field2 =20202 Field 3=11111 Field4=zzzzz etc.
Row 4
Field1 = 54321 Field2 =xxxxx Field 3=yyyyy Field4=zzzzz etc.
Etc. Etc.
I want to be able to find the duplicates for Field1 and then delete all but 1 of those rows.( I don't care which one I keep just so only one is left.) The data in the other fields may or may not be unique.
I know how to find the duplicates it's just the deleting part I am having problems with. Any help would be much appreciated. Thanks,
Kerry
I am sorry if this is the wrong forum for this question but I can not find better fit.
I am using Visual Studio 2005 to write an asp.net web site which includes an SQL data base. In the databaseI have a table called 'ACCOUNTS' which has primary key column called 'ID' and a nvarchar column called 'Name'.
I want the values in the 'Name' column to be unique in the table so that all accounts have unique names. I don't want to make the 'Name' column the primary key because I want people to be able to change the name of their account after it has been created.
It seems to me there must be some way to add such a constraint to the table but I can find none. I have searched the documentation and seen loads of references to unique columns but I can't find any way to set a column to unique thru the IDE (or any other way). Can anyone help me?
Thanks in advance for any help.
I have a table in which a non-primary key column has a unique index on it.
If I am inserting a record into this table with a duplicate column value for the indexed column, then what will be the error number of the error in above scenario? OR How could I find this out?
I am having issues trying to write a query that would provide me the unique GUID numbers associated with a distinct PID if the unique GUID's > 1. To summarize, I need a query that just shows which PID's have more than one unique GUID. A PID could have multiple GUID's that are the same, I'm looking for the PID's that have multiple GUID's that are different/unique.
Table1
GUID PID
GUID1 PID1
GUID1 PID1
GUID1 PID1
GUID2 PID1
GUID3 PID2
GUID3 PID2
GUID3 PID2
The result of the query would only have PID1 because it has two unique GUID's. PID2 would not be listed has it has the same GUID3 in each row.
Result:
PID1
Hello everyone, I am new to ERWIN and I need helps from the experts outthere.We are using ERWin 4.1.2771 and have reversed engineered some MS SQLServer 2000 databases.The problem we are having is that we have a FK on a column to a tablewhere the PK of the referencing table is on another column (such as anidentity column). We have a unique index on the column in the PK tableand SQL Server allows you to build a FK reference even though thecolumn is not defined as the PK.Does anyone know how to create this type of FK within Erwin?Thank You
View 2 Replies View RelatedI am looking for advice on how to handle the following situation.Data being saved to a database table. Unique index on column in table. If the constraint is violated, what is the best way in alerting the user? Catch the exception and display error message to user? How do I know the exception is because of a unique index violation?
View 1 Replies View RelatedHi,
How can I assign or retrieve a unique ID from a msSQL table where there isn't a unique column.
Is there a hidden 'raw id' i can retrieve from msSQL for each record?
I'm not allowed to change the current database at all, but the developers who created it managed to use it without Unique IDs.... which doesn't help me coming from a mySQL background where UID's are kind of mandatory.
Thanks
This is for SQL 2000 (SP 2) using Enterprise Manager. I have a table with a unique index comprised of several int fields. The index needs to include an additional bit field that is part of the table. But when I go to modify the index, the bit field name doesn't appear in the Column Name list.
Can anyone shed any light on the problem?
Thanks.
hi,
In the below code, i am trying to insert the identity value into the 2nd column of the Symp_Quote table. Schema of the table Symp_Quote is as follows.
CRAETE TABLE Symp_Quote
(
QuoteRevision_ID INT PK,
DocumentSeq TINYINT PK,
DocumentDisplayName VARCHAR (255)
)
1st column data is coming from the select statement. but for 2nd column i have to insert identity or some unique value. to do that i was trying withe the following code, but it failed.
INSERT INTO Symp_Quote
(
QuoteRevision_ID,
DocumentSeq,
DocumentDisplayName
)
SELECT
S.QuoteRevision_ID,
IDENTITY (INT,1,1) AS DocumentSeq,
T.Name,
FROM Symp S
INNER JOIN Trilogy T
ON
SQR.tril_gid = FQR.tril_gid
kindly correct me whr I am wrong.........
Thanks,
Rahul Jha
I like to use the management console to design tables, but have
been unable to figure out how to add a unique column constraint.
I have a primary key defined in col1 and want to make col2, char(10),
unique, but keep col1 as the primary key.
I have tried the Check constraint menu path, but do not know how
to write the expression.
If I go along the Indexes/Key path, I am forced to change the primary
key which I do not want to do.
Can anyone help?
TIA, Joe
I just started with SQL Server Express and was playing around with the Management Studio.
I created a query to make a column unique:
ATLTER TABLE Table_Name
ADD UNIQUE (Column_Name)
That worked perfect, if I fill in the same data in the next row of that column, I get an error as expected. But, how do I remove the UNIQUE property on that column? I can find it in the column properties within Management studio, neither I can't find the command to undo this action.
Hello!
This is not really a question, maybe a start of discussion if this could be a feature in coming versions...
Let's say I have a table like this:
id text1 text2
int varchar varchar
1 abc ghi
2 abc ghi
3 def ghi
and I want to query using group by, I would write SELECT text1, text2 FROM table GROUP BY text1, text2
This is not too bad, but imagine I got several columns of text I need to query, then I need to put them in both places, SELECT and GROUP BY. If I do use functions like SUBSTRING or concatenation on the columns, I need to repeat these in the GROUP BY as well.
It would be nice if I could say "GROUP BY id", and since id is unique on the table it is obvious that all columns taken from this table are already grouped and can be queried without having to have them in group as well.
For functions this is of course only possible if all parts only refer to columns from this table, or absolute entries, like LEFT(varchar ,3) or int+1 or such.
With this example, the benefit is not visible: if I added the id to the GROUP BY, I get every single row of course. GROUP on unique is useless... It does make sense if you query another table and join in this one as a lookup, and the id gets "multiple" this way.
As I said, just an idea to make the GROUP BY section more transparent and avoid repeating sections!
I'd like to know what you think of that.
Ralf
I have a producer table with a nullable column that stores SSN's. In some cases producers inherit SSN's from other producers. These records will have a null producer.ssn and a record stored in a child table to track the inheritance. Anyway, I've found two techniques to enforce uniqueness on a nullable column and wanted to get opinions as to which was better. First, write a trigger. Second, create a computed column that has a unique constraint on it. The computed column would use the SSN if not NULL Else use the PK identity value of the record. EXAMPLE DML:CREATE TABLE test ( ssn CHAR(9) NULL, testId INT identity(1,1) NOT NULL, ComputedConstraint AS CASE WHEN ssn IS NULL THEN CAST(testId AS CHAR(9)) ELSE ssn END, UNIQUE (ComputedConstraint)) Any comments would be greatly appreciated.
View 6 Replies View RelatedI am very new to sql and I have got this doubt.
CREATE TABLE employee
{ EMP-ID VARCHAR UNIQUE,
DESIG VARCHAR UNIQUE,
SALARY INT };
Probably this is worst table ever created :).Now How to drop UNIQUE constraint on only column lets DESIG.
if i write
ALTER TABLE employee
DROP CONSTRAINT myuniqueconstraint;
will drop constraints on both columns which I dont want.
Any query that remove UNIQUE CONSTRAINT only on DESIGN column.
and one more clarification,
ALTER TABLE employee
MODIFY SALARY INT NOT NULL;
ALTER TABLE employee
ALTER COLUMN SALARY INT NOT NULL;
which of the above two is right query to add NOT NULL constraint to the above employee TABLE?
Hi All,
I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);
I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
I don't immediately find if this is possible but hope someone can give me an answer:
is it possible to make a unique constraint over 2 columns but only when 1 column has a specific value ?
Example: table (tableid, instancetype, instancename, ..)
instancetype can be A or B
if it is A then instancename must be unique
but for B instancename is not unique as these are copies from A
only solution I can think of is to make a trigger on an insert to check what the instancetype is and do a select to see if the name already exists in the table or not..
are there other solutions to make a constraint like this ?
Aeneas.
Is it possible to create a unique constraint to a column from anothertable? For example:tb_current:current_names--------------aaabbbtb_new:new_name--------cccNow I want to create a constraint on tb_new.new_name to be unique withrespect to tb_current.current_names. However, tb_new.new_name shouldnot be unique to itself. So I should not be able to insert 'aaa' totb_new.new_name. But I should be able to insert 'ccc' totb_new.new_name.Here's the script to reproduce this example:create table tb_current(current_names varchar(10))create table tb_new(new_name varchar(10))insert tb_current values ('aaa')insert tb_current values ('bbb')insert tb_new values ('ccc')select * from tb_currentselect * from tb_newinsert tb_new values ('aaa') -- this should NOT be allowedinsert tb_new values ('ccc') -- this should be allowed
View 3 Replies View RelatedHello there,
I am testing out the performance benefits of utilizing include column(s) to avoid RID lookups and so far it looks very promising. I am able to reduce the cost of the query significantly and execution time is very good. However my non-clustered index is part of the primary key constraint and I am wondering if it is possible to create the include column as part of the primary key. Or do I have to drop the pk constraint and create unique non-clustered index with include column?
Thanks,
Sankar
I am in the "Check Constraints" window trying to add a constraint that during inserts only allow distinct Column2 insertions. What is the Expression for that? I tried UNIQUE(Column2) it give me an error.
Here is my table definition:
Column Name Data Type Allow Nulls
--------------------------------------------------------------------------------------------
Column1(PrimaryKey) int No
Column2 nvarchar(MAX) No
Hi All,
I am having problems with unique contraint, say I have a table called customer
create table customer (
id int identity(1,1) not null,
name nvarchar(50) not null,
id_card_no nvarchar(20) unique,
);
so when I do mutilple null inserts
insert into customer(name, id_card_no)
values("Customer 1", NULL);
insert into customer(name, id_card_no)
values("Customer 2", NULL);
I get an error duplicated keys on the unique column.
In Oracle DB, unique columns are behaved a little different where multiple nulls are allowed in a unique column.
I know there are some tircks that you can do to allow multiple nulls in unique column in sql server(well, pretend to be) like creating a computed column, create a view and an index...
Are there any ways to archieve that in sql sever compact?
Hi,
I have a table with one of its column VARBINARY(MAX).
I want to make sure that the values in this VARBINARY(MAX) column is unique.
SQL Server doesn;t allow to create Unique Constraint over VARBINARY fields - whats the best workaround for ensuring uniqueness on VARBINARY columns.
Thanks,
Loonysan
I am using Access 2007 and I have 10 columns of data.
I am trying to select all the data from all 10 columns where the first column's data returns only the Unique values and the other columns return all the data from the row that is returned from the first column.
I have used this
SELECT DISTINCT [SFR Rates All].Sorter, [SFR Rates All].SProgram, [SFR Rates All].S_Price, [SFR Rates All].Min_Loan, [SFR Rates All].Max_Loan
FROM [SFR Rates All];
But that returns all the data in the table and all values in the Sorter column with duplicates because each row has distinct data.
I am trying to return unique values from [SFR Rates All].Sorter (the first column) and all the data from the other 9 columns that are contain the row with the unique value in Sorter.
I know that I am missing something basic but well, I can't figure it out.
Thanks
Bob
Hi, can anyone guide me on how to create a unique field with SqlExpress and VC#.net?
I know how to create primary key with identity, however I need to create another unique column for "email". I have search high and low for a solution but found none, the closest i got is unique constraint which I've not idea what's that.
Please help, else I have to always do a duplication check before inserting new records.
Many thanks.
How to drop the unique key column in sql.
View 2 Replies View RelatedI have table with some columns with no primary key..
I have to find the combination of columns which may the row unique...
How to do that like microsoft...
Hi,
Can anyone help me?
when I am inserting a record to a dataset I am getting Column 'FieldID' is constrained to be unique. Value '0' is already present.
I have 2 fields in table like FieldID,FieldName where FieldID id is Primary key and identity column
I am not inserting any value to FieldID as it is identity column.
DataRow newDR= dbDataSet.Tables[strTable].NewRow();for (int i = 0; i < e.Row.Cells.Count; i++)
{ if ( e.Row.Cells[i].Key != "FieldID" )
{if (e.Row.Cells[i].Value != null)
{
newDR[e.Row.Cells[i].Key] = e.Row.Cells[i].Value;dbDataSet.Tables[ strTable].Rows.Add(newDR);
}
}
Some times it is woking fine with out any error............
thanks..
Hi all,
I am trying to add a unique index/constraint on a column that allows NULL values. The column does have NULL values and when I try to create a unique constraint, I get the following error.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 9. Most significant primary key is '<NULL>'.
Are'nt you allowed to create a UNIQUE constraint on a NULL column? Books Online says that you are allowed to create a unique constraint on NULL columns, then why am I getting this error.
Any help would be appreciated.
Thanks,
Amir
I've seen this error on several of my databases again and again this week:
Violation of PRIMARY KEY constraint 'PK_XXXX'. Cannot insert duplicate key in object 'dbo.MyTable'. The duplicate key value is (whatever number here). The statement has been terminated.
The thing is, the PK is unique, and the INSERT statement does not touch that column, it touches the other ones. So SQL2012 is the one that automatically generates the next and unique available value.
How can be possible that the value to be inserted (generated by SQL2012) is a duplicate one? By the way, this was not happening on the previous SQL2005 or SQL2008 server where the databases were being hosted.
Here's my SQL 2012 version: 11.0.3000.0 (SP1 applied). Runs on top of a Win2008R2 Cluster.
Need help,
I'm a newby to SQL and looking for how-to-help.
I have an existing DB and within a certain table, I have created a new Column via Studio manager, but need help with the following:
Need to make the new Col "Unique, and Indexed" but cannot see anywhere in studio manager interface to do it.
Thanks.
Hi, all,
I have a question about adding a unique key column to an existing table.
what i trying to do is that: I have already created a table, now i wanna add a ID column to this table, and generate the values for ID column from 1 to the existing row number. How can I get this done?
Thanks a lot in advance for any guidance.
Hi all,
I've got a large table (3mil records) with a number of columns, but currently no way to refer to any individual column. I therefore need a primary key, but does anyone know of a SQL statement I can use that will create a column (say, ID) that is automatically filled with an incrementing 'counter'? Or, instead, how can I set unique incremental values after first creating the column?
Many thanks,
Graham
I have the following two tables...
tblServer
-serverID
-serverName
-serverLocation
tblSite
-siteID
-serverID
-siteName
-siteIpAddress
I need to write a select query that gets the values of all columns but only returns unique sites because some sites are load balanced across several servers and where this is the case I don't want the site to appear multiple times in the list.