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.
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.
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.
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.
I'm brand new to web and ASP programming, but experienced with VB/VB.NET and SQL Server 2000 programming. I followed a tutorial and built a little website that lets you create a user login, login and change your password. I built this in Visual Studio 2005 using VB code and my understanding is that the site uses a SQLExpress database. There's an aspnetdb.mdf and .ldf file in the App_Data folder. I want to deploy the website to a web server that is hosting SQL Server 2005, not express. Can someone be explicit about steps to take in order to do that? I've been trying a number of things in the web.config file, but I cannot seem to make a connectionstring that works. I also don't know what to do with the .mdf and .ldf files from the development server--I'm thinking they should not be deployed. Thanks for anything.
I am new to MS SQL and I was wondering is it possible to create a table with unique rows?? By this I mean if a table has two columns then a duplicate row would be if BOTH columns matched two columns of another row.
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
Hi all,I might be getting this all wrong but bear with me. I need to create some kind of Unique field in my DB that is nonsequential. This is because I need it to be difficult to guess ids if you have an example in front of you.I have looked at 8digit EAN codes which include a check digit system.( I use a base digit of the row_id for these) Can anyone tell me how many uniques I can get out of this system?For my ID: I have looked at something along the lines of:
Hex(row_id) + "T" + Hex( Trimmed(EAN) ) The "T" serves to split the numbers for when I am converting back. So for example:row_id EAN_code Hex(row_id) + "T" + Hex( Trimmed(EAN) ) ------------------------------------------------------------------------------------------ 3166 00031663 C5ET7BAF 3167 00031673 C5FT7BB9 3168 00031686 C60T7BC6
Is this too easy to guess (once you can tell there are two hex numbers there?) What do people think? Thanks,Pete
I am attempting to create a unique constraint on an nvarchar field named theology (it is not the primary key field) that allows nulls. The table contains multiple rows with the value of null for field theology. The documentation says one can create a unique constraint on a field with all unique value except for null. Here is the error message:
'testtable1' table - Unable to create index 'IX_testtable1'. ODBC error: [Microsoft][ODBC SQL Server Driver][SQL Server]CREATE UNIQUE INDEX terminated because a duplicate key was found. Most significant primary key is ''. [Microsoft][ODBC SQL Server Driver][SQL Server]Could not create constraint. See previous errors.
Any ideas? I am creating a unique constraint and not a unique index. Is there some other database option to set to allow this?
I have a table where the key is an autonumber. I also have a field which holds the reference of a room eg 0BM1. It is nvarchar. Is there a way I can set this field to duplicates = No, so that my user cannot enter the same room reference more than once. Or do I have to do this check in my asp.net code ?
I have created the companyid as Primary Key.How to create a unique secondary index on Company Name. To avoid inserting duplicate records in database with the same companyname. I m creatin database in sql server 2005 with asp.net C# 2005. I know one way is write the query if not exists at the time of insert.But,i want to know is there anyother way to make a unique secondary index for the companyname on the company tablethanxs
Hi there,I am trying to create a UID that is unique within my SQL Server. There are many users accessing the Server in seperate databases, but then I want to combine all the data from these tables, keeping the ID from each one as a primary key. I have written the following function, but when i call it as a default value for a field, it does not produce a unique number. CREATE FUNCTION GETNEXTID(@CURDATE DATETIME)RETURNS BIGINTASBEGINRETURN (SELECT CAST(CAST(DATEPART(YY,@CURDATE) AS VARCHAR) +RIGHT('0' + CAST(DATEPART(M,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(D,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(HH,@CURDATE) AS VARCHAR),2) +RIGHT('0' + CAST(DATEPART(SS,@CURDATE) AS VARCHAR),2) +RIGHT('00' + CAST(DATEPART(MS,@CURDATE) AS VARCHAR),3) AS BIGINT))END Can anyone help?
Hello. Could anyone tell me why it is not possible to create a foreign key on two columns those references on 2 columns in another table? Those 2 columns have each a unique constraint.
I have: CREATE TABLE T_PK (ID1 INT CONSTRAINT CHK_UNIQUE1 UNIQUE,ID2 INT CONSTRAINT CHK_UNIQUE2 UNIQUE)
why it is not possible to create a Foreign key to a Unique constraint?
Table A has column 1 holding a Primay key and two columns (2 and 3) holding a Unique combination (and some more columns).He created an Unique constraint on column 2 and 3 together.
He wanted to use this Unique combination to point to table B (instead of the table 1's PK) so he tried to create a foreign key on a column in table B but an error popped up prompting;
The columns in table 'TABLE_A' do not match an existing primary key or UNIQUE constraint.
Ok - these two columns ar no PK but the hold an Unique constraint......
Introduction This MS SQL Store Procedure solves a problem which is not common except when you have a table in a database which each row needs to be uniquely identified and their are more rows in the table than you can identfy with a big int or Unique Identifier.
So for example,
if you used a "unique identifier" you would be limited to 8.6904152163272468261061026005374e+50 unique rows at best. If you used a "Big Int" you would be limited to -2^63 ( -9223372036854775808) through 2^63-1 (9223372036854775807).
This method will allow you to have 2.2528399544939174411840147874773e+106. (With cluster indexing the identity field.) or, 4.722366482869645213696e+129 (Without indexing the identity field)
Why would you need that many unique values? Well, the reason for this invention is due to the need to track every email that an application sends for Sarbanes/Oxley Requirements. With this technique, every email sent out will be uniquely identified for a very very very long time.
The purpose of this is to show how to set up an identity column with a larger range than a big int or unique id. Try transaction logs where you need to track every change, or determining click paths through a website, etc.
The point is, that this method pretty much does what the title says, "Create unlimited Unique ID's". What table you apply this too and for what reason is up the the programmer.
Background This design uses basic counting methods and handles the limitations of MS SQL TSQL. First, you can use a varchar(4000) as the unique id column but the issue with this is that as of MSSQL 2000, the largest indexable field is 900 character. So if you need to be able to quickly search the table by key, or clustered keys, you need to limit your key column with to 900 characters, otherwise if you use a varchar(4000) make sure when searching the table you create a temporary table, select the subset into it and search that.
Using the code First, copy and paste all the TSQL into a Query Window and compile it in the database you wish to use it in.
[Code] /********************************************************************************** Program: ALTER Unlimited Unique ID's (Auto Increment) Programmer: Vince Gee Date: 9/28/2005 Parameters: @TABLE_NAME - The name of the table to establish the auto incrementing field in @COLUMN_NAME - The column name in the table to establish the auto incrementing field in @DROP_EXISTING_TRIGGER - Whether or not on running to drop an existing trigger with the same name. Theory: A varchar 900 field will be able to have 2.2528399544939174411840147874773e+106 unique identifiers in it.
A uniqueID only has 8.6904152163272468261061026005374e+50 unique identifiers in it.
Description: The purpose of the sql procedure is to automate the creation of auto updating identities on a sql table without the trouble of writing the trigger each time.
So what does this do? Well for example lets say we have the following table which you will have many many many rows in.
myKey is the unique identifier for each row. We can set it's size really to anything, This proc will look for the column specified and determine it's size. The column should be nvarchar of type
All the other columns don't matter, the only issue is if all the column names concated together exceed the storage compacity w/ the trigger code of 4000 characters. If this is the case your gonna have to write the trigger manually.
So to set the auto incrementing field up you would call this proc: Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey' or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Output: When data is inserted into the table, the auto incrementing field will look like 0000000001 0000000002 0000000003 0000000004 0000000005 0000000006 0000000007 0000000008 0000000009 000000000A 000000000B 000000000C 000000000D 000000000E 000000000F 000000000G 000000000H 000000000I 000000000J 000000000K 000000000L with how many 0's set up etc. It goes 0-9, then A-Z
--If the trigger if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[' + 'IO_Trig_INS_' + @COLUMN_NAME + ']') and OBJECTPROPERTY(id, N'IsTrigger') = 1) begin IF @DROP_EXISTING_TRIGGER = 0 BEGIN -- USER DOESN'T WANT US TO AUTODROP THE TRIGGER, BY DEFAULT AUTODROP TRIGGER IS OFF PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT 'STOP ERROR :: PLEASE DROP THE EXISTING TRIGGER BEFORE RUNNING THIS PROC' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' RETURN END ELSE BEGIN --CREATE A SQL STRING TO DROP THE TRIGGER SO WE CAN RECREATE IT. set @EXECSQLSTRING = 'drop trigger IO_Trig_INS_' + @COLUMN_NAME --EXECUTE THE SQL EXEC SP_EXECUTESQL @EXECSQLSTRING END end
--CREATE A TABLE TO HOLD THE RESULTS FOR THE SP_COLUMNS create table #temp ( TABLE_QUALIFIER varchar(255), TABLE_OWNER varchar(255), TABLE_NAME varchar(255), COLUMN_NAME varchar(255), DATA_TYPE int, [TYPE_NAME] varchar(255), [PRECISION] int, LENGTH int, SCALE int, RADIX int, NULLABLE int, REMARKS varchar(255), COLUMN_DEF varchar(255), SQL_DATA_TYPE int, SQL_DATETIME_SUB varchar(255), CHAR_OCTET_LENGTH int, ORDINAL_POSITION int, IS_NULLABLE varchar(255), SS_DATA_TYPE int ) --POPULATE THE TEMP TABLE W/ A SP_COLUMNS ON THE TARGET TABLE insert into #temp exec sp_columns @TABLE_NAME
--CYCLE THROUGH ALL THE COLUMN NAMES AND BUILD OUR COLUMN NAME STRING --FOR INSERTS. THE LAST COLUMN NAME IS ALWAYS THE IDENTITY FIELD. SELECT @MAXORDINAL = MAX(ORDINAL_POSITION) FROM #TEMP SET @COUNTER = 1 SET @COLUMN_NAMES = '' WHILE @COUNTER <= @MAXORDINAL BEGIN select @tCOLUMN_NAME = COLUMN_NAME FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER if (@tCOLUMN_NAME <> @COLUMN_NAME) begin SET @COLUMN_NAMES = @COLUMN_NAMES + @tCOLUMN_NAME+ ',' end else begin select @KEYLENGTH = LENGTH FROM #TEMP WHERE ORDINAL_POSITION = @COUNTER end SET @COUNTER = @COUNTER +1 END --CLEAN UP drop table #temp
IF @KEYLENGTH > 900 Begin PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!WARNING:: YOU WILL NOT BE ABLE TO INDEX THIS TABLE BY YOUR CHOSEN COLUMN,!!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!BECAUSE THE COLUMN IS OVER 900 CHARACTERS. 900 CHARS ARE THE MAX THAT !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!THAT CAN BE INDEXED !!!!!!!!!!!!!!!!!!!!!' PRINT '!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!' END SET @EXECSQLSTRING = ' CREATE TRIGGER IO_Trig_INS_' + @COLUMN_NAME + ' ON ' + @TABLE_NAME + '
END' if len(@EXECSQLSTRING) <4000 begin EXEC SP_EXECUTESQL @EXECSQLSTRING end else begin print 'STOP ERROR:: BUFFER OVERFLOW. THE GENERATED TRIGGER TEXT > 4000, Trigger must be hand written.' end
GO SET QUOTED_IDENTIFIER OFF GO SET ANSI_NULLS ON GO
First, to test the functionality create a temp table.
First, to test the functionality create a temp table.
Second, call the proc on the table. Parameters are:
Table Name - Name of the table to put the trigger on. Column Name - Name of the column to use as the key. Drop Existing Trigger - If this is set to 1 and a trigger with the name generated by this stored procedure exists it will drop it. (OPTIONAL)
Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey'
or Execute SP_SET_UNIQUE_FIELD 'Countertest','myKey',1
Now, we are going to test how this works. Copy and paste the following code into a query analyzer.
declare @t int set @t = 0
while @t <= 40000 begin insert into countertest select '','s','s','s' set @t = @t + 1 end GO
Once this completes, you can inspect the unique id by selecting it from the table SELECT RIGHT (MYKEY,10) FROM countertest
The table will keep incrementing the key column first 0-9 then a-z. When it reaches all 'z' it will roll over, but if your key column is wide enough this shouldn't happen while your still employeed.
Also, the stored procedure figures out how wide the key column is automatically and adjusts the script accordingly. So if you widen or shrink the key column, just rerun the proc to update the trigger script.
Hello, I will explain myself further. I want to make my table in such a way that no two colums have the same value for example: Row 1 - Column 1 = "cool" Row 1 - Column 3 = 91 Row 3 - Column 1 = "cool" Row 3 - Column 3 = 91
I dont care about one column having duplicate values, I want to protect against Column 1 and 3 having the same values on other rows. Is this possible to do in sql server?
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
Hi all,My program is a central data processing application built in ASP.We have different companies that use different web pages on another webapplication (from different countries) to load some inventory data(merchandise - clothes).Here is my requirement: Load different types of data (in differentformats) into a common set of tables, to do this I have to firstfilter, do lookup's, use cross-reference tables on this data and thenload it into a couple of tables.Since data is so different everytime, I want to have one main storedprocedure in which I can build the table (I know the format of thisdata so I know what columns/types to create) into which I will feed thedata.After this I will create other stored procs/udf's that reference thistable (probably from the same main stored proc) 'cleanse' the databefore loading into the actual tables.How feasible is my approach? (if you can call it one :-) I do not wantto have seperate tables for each country, that will be too many and sothis plan...If not any other ideas will be really helpful.thanks much
I have the following table (Table does not have unique key id )
Last Name First Name DATE Total-Chrg
Jaime KRiSH 5/1/2015 -4150.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 1043.66 Jaime KRiSH 5/1/2015 4150.66 Jaime KRiSH 5/3/2015 4150.66 Peter Jason 5/1/2015 321.02 Peter Jason 5/1/2015 321.02 Peter Jason 5/23/2015 123.02
I want the results to be in following way
Uniq ID Last Name First Name DATE Total-Chrg
1 Jaime KRiSH 5/1/2015 -4150.66 2 Jaime KRiSH 5/1/2015 1043.66 2 Jaime KRiSH 5/1/2015 1043.66 3 Jaime KRiSH 5/1/2015 4150.66 4 Jaime KRiSH 5/3/2015 4150.66 5 Peter Jason 5/1/2015 321.02 6 Peter Jason 5/1/2015 321.02 7 Peter Jason 5/23/2015 123.02
May be we may do by dense_rank or Row_Number, but I couldn't get the exact query to produce based on the above table values. There are some duplicates in the table(which are not duplicates as per the Business). For those duplicated Unique ID should be same(Marked in Orange Color which are duplicates).
I am looking to create a script that will go through a table a pick out the necessary columns to create a unique record. Some of the tables that I am working with have 200 plus columns and I am not sure if I would have to list every column name in the script or if they could be dynamically referenced. I am working with a SQL server that has little next to no documentation and everytime I type to mere some tables, I get too many rows back.
I 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?
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.
ReportModel is created ,there is only a named query in DSV ,it has a few tables in it(The relationship are inner joins and outer joins).
The question is how could I create a unique logical primary key to identify each unique row in the named query dataset, and also you cannt generate a model unless the named query has a logical primary key . how can I solve this problem,any help?
The SQL ERRORLOG shows: Error is 3414, Severity 21, State 2 and says: "An error occurred during recovery, preventing the database 'model' (database ID 3) from restarting." Just prior to this, I get a warning: "did not see LP_CKPT_END".
Any thoughts why this might be and how I can fix this?
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.
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