What 's Difference Between Unique Key And Unique Index
What 's difference between Unique key and unique index in SQL server 2005?
View Complete Forum Thread with Replies
Related Forum Messages:
Unique Constraint And Unique Index, What's The Difference?
What's the difference in the effect of the followings: CREATE UNIQUE NONCLUSTERED INDEX and ALTER TABLE dbo.titles ADD CONSTRAINT titleind UNIQUE NONCLUSTERED I found there're two settings in Indexs/Keys dialog box of the management studio, Is Unique, and Type. The DDL statements above are generated by setting Is Unique to yes plus Type to Index, and just Type to Unique Key, respectively. What's the difference between them?
View Replies !
What's The Difference? Unque Constraint And Unique Index, Etc.?
All,What's the difference between a unique contraint and unique?sementically, if you want a column contain unique values, it is acontraint. And an index is for searching/sort. The questions are:1. Does a unique constraint interally use unique index?2. If Yes to #1, I DO NOT need to create an index for search/sortpurpose, right?3. If Yes to #2, What's better?4. Also for Primary Key column, it is actually a special uniquecontraint. Not need to create index on PK column for searching/sorting,correct?5. Also for FK contraint, no need to create an index forsearching/sorting?ThanksJohn
View Replies !
Unique Constraint Vs Unique Index
BOL says a unique constraint is preferred over a unique index. It also states that a unique constraint creates a unique index. What then is the difference between the two, and why is a constraint preferred over the index?
View Replies !
Unique Index Vs Unique Constraint
Hi everyone, I need urgent help to resolve this issue... As far as the performance goes which one is better.. Unique Index(col1, col2) OR Unique constraint(col1, col2) ? Unique constraint automatically adds a unique index and unique index takes care of uniqueness then whats the use of unique constraint ? Which one do one use ? thanks sonali
View Replies !
Difference Between A Primary Key And A Unique Key?
hello all sorry m asking Database Question .. m talking about SQL SERVER 2005 but i have bit confusion... what difference between a primary key and a unique key? if m not wrong then primary key doesn't allow NULLs, but unique key allows NULLs but in many searches comes that primary key doesn't allow NULLs, but unique key allows one NULL only. what this means 'one NULL only'?? according to my practical we can give more then 1 nulls... plz clear this point???
View Replies !
Unique Index
Just found out that creating a unique index does not create a unique constraint, but creating a unique constraint creates unique index. But effectively they do the same thing.
View Replies !
Unique Index
if this question is inappropriate here, I apologize (it's at least obliquely related). I have been using ssno as a unique key in a datawarehouse I have been working on because all of the component systems have had it. I now have a database to add where ssno is not available. I have first, last address, city, state,zip and dob. Question is, how to construct a unique identifier from those components. If not unique, then at least usable? Again, if this post is wrong here, I apologize Thanks for any input Walter
View Replies !
Unique Index
How to findout whether a Index is unique or not? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
Can't Add Bit Column To Unique Index
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.
View Replies !
Creating A Unique Index
HiI tried the following from the help file...When you create or modify a unique index, you can set an option toignore duplicate keys. If this option is set and you attempt to createduplicate keys by adding or updating data that affects multiple rows(with the INSERT or UPDATE statement), the row that causes theduplicates is not added or, in the case of an update, discarded.For example, if you try to update "Smith" to "Jones" in a table where"Jones" already exists, you end up with one "Jones" and no "Smith" inthe resulting table. The original "Smith" row is lost because anUPDATE statement is actually a DELETE followed by an INSERT. "Smith"was deleted and the attempt to insert an additional "Jones" failed.The whole transaction cannot be rolled back because the purpose ofthis option is to allow a transaction in spite of the presence ofduplicates.But when I did it the original "Smith" row was not lost.I am doing something wrong or is the help file incorrect.Dan
View Replies !
Unique Constraint Index
When I add a unique key constraint to column in SQL 6.5 why does it alsocreate an index. e.g. In the table subaccounts I added a unique keyconstraint for the column login and SQL creates an index with the nameUQ_SubAccounts_2__19 (UKC).Does this also mean that there is no need to create an index for thiscolumn?thxMansoor
View Replies !
UNIQUE INDEX If Not NULL
Hello ! for MS SQL 2000 how can i set an unique index on Serial column but only if Serial IS NOT NULL CREATE UNIQUE INDEX [IX_Product] ON [Product]([Serial]) ON [PRIMARY] i can have 100 rows with a NULL Serial thank you
View Replies !
Unique Constraint/index
I'm trying to weight the pros and cons of unique constraints and unique indexes. I understand that creating a unique constraint also creates an index. If that is the case, why not just use a unique index? Could someone give me an example of when you would want an unique constraint over an unique indexes Thanks in advance
View Replies !
Better Performance With Unique Index?
I have read that you get better performance with unique indexes rather than non-unique indexes. I have experimented with this in SQL 2000. I have two identical tables (with about 250000 rows each) with a 12-character unique column. In one table I define it as a regular index and in the other I define it as a unique index. No matter what I try I get identical performance, and the query optimizer shows an identical plan. I even tried clauses such as WHERE 1 < (SELECT COUNT(*) FROM TheTable WHERE key_column = OtherTable.key_column) which should obviously return nothing if TheTable.key_column is unique. However the query still ran a long time no matter if the index is unique or not. I have also tried a unique constraint instead of a unique index and got the same (non)results. Can anyone come up with an example where creating a unique index actually makes a performance difference?
View Replies !
Double Unique Index
Hello. I have a question. I need to make a double unique index on a table. for example: I have 2 columns, ColumnA and ColumnB. ColumnA can have duplicate values, so is ColumnB, but it should be impossible to have duplicate values on both columns. for example: Row 1: ColumnA = 1, ColumnB = 2 Row2: ColumnA = 1, ColumnB = 2 this shouldn't be possible. Row1: ColumnA = 1, ColumnB = 2 Row2: ColumnA = 1, ColumnB = 3 this should be possible is there any way I can do this? thanks in advance
View Replies !
Unique Index, SQL 2005
Is there a simple way to find properties of an index for a table? Specifically I am looking for the unique property. I currently have code to to do this, however I feel like there must be some easier way. I am using .net 2005 and SQL 2005 coding in c#. Thanks for any help! Code Snippet _conn.Open(); SqlCommand cmd = new SqlCommand("sp_helpindex", _conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@objname", "TABLENAME")); SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(results); foreach (DataRow row in results.Tables[0].Rows) { ArrayList names = new ArrayList(); DataTable dtUnique = new DataTable(); SqlCommand cmd2 = this._conn.CreateCommand(); cmd2.CommandText = String.Format("SELECT INDEXPROPERTY(OBJECT_ID('TABLENAME'), 'INDEXNAME', 'IsUnique') AS isUnique"); dtUnique.Load(cmd2.ExecuteReader()); bool unique = Convert.ToBoolean(dtUnique.Rows[0]["isUnique"]); if (unique) names.Add(tableName); } _conn.Close();
View Replies !
PK Vs Unique Clustered Index
When not using any enforced relationships in a database (which I know we should, but I like most of you, have inherited sub-optimal design), is there any real difference between using a Primary Key (to which no foreign keys are tied) and using a Unique Clustered index? Thanks for your thoughts
View Replies !
Unique Constraint/Index
What is the difference between unqiue constraint and unique index? What are the pros and cons? Are they interchangable? ------------------------ I think, therefore I am - Rene Descartes
View Replies !
How To Create Unique Secondary Index ?? HELP
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
View Replies !
Unique Index For Two Columns In A Table
Hi,I would like to add a unique index that consists of two fields in atable.e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combinationmust be Unique.Can anyone tell me the actual sql syntax to create this index?Thanks,June.
View Replies !
Create Unique Index Error
Does anybody know the significance of 3 in the following error message? "CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 3. " Thank you
View Replies !
Unique Index On X Columns But Not Primary Key
my table : CREATE TABLE [dbo].[users] ( [ID] [int] NOT NULL , [A1] [nvarchar] (100) NULL , [A2] [nvarchar] (100) NULL , [A3] [nvarchar] (100) NULL ) ON [PRIMARY] i must keep ID columns as primary key ALTER TABLE [dbo].[users] WITH NOCHECK ADD CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [ID] ) ON [PRIMARY] but now A1+A2 must be unique how can i do it ? thank you
View Replies !
Replication- Converts PK To Unique Index
Hi, When I replicate from a 7.0 publication to a 2000 subscription though a 2000 distribution, all the primary keys gets converted to unique clustered index in the subscription. Whereas , when I use 7.0 distribution keys stay keys in the subscription. Can you suggest how can I fix the problem? Is it a bug? Thanks in advance. Hassan
View Replies !
Unique Index With Null Values
Is there any equivalent to MS Access unique index that allows null values ( ignore null values) in SQL Server? how can i create a unique index that ignores null values? Thanks in advance.
View Replies !
Error While Droping Unique Index
Hi, I get the following error message while droping unique index. Server: Msg 3723, Level 16, State 5, Line 1 An explicit DROP INDEX is not allowed on index 'dbo.ALEG.IX_ALEG'. It is being used for UNIQUE KEY constraint enforcement. Could you please how the index can be droped. Thanks
View Replies !
How Do You Determine An Index Is Unique From Sysindexes?
I need to write a script which will respond differently if a table's index forces uniqueness. I am using SQL 7.0 and I am using the [sysindexes] table of my database to examine those indexes. I am using the [id] field from the [sysobjects] table for the table I am examining and I am using that [id] field to find like-valued [id] fields in the [sysindexes] table. My problem is that I can't seem to determine, from an examination of the [sysindexes] table, whether, or not, an index is unique. When adding an index, and checking the unique property of that index, the values that I get in the [status] field of [sysindexes] are different from the values which are supposed to show, at least according to the sysindexes (T-SQL) entry in SQL Server Books Online. For instance, I have a table which combines two text fields which comprise the only key on a table. The unique and clustered boxes are checked in the index setup screen. When I look in the [sysindexes] table, the value in the [status] field is 2113554, which is not a value I see in the books online page. According to my books online page, a unique index should have a value of 2 in the [status] field and a clustered index should have a value of 16 in the [status] field. My assumption is that I should see a value of 18 in that [status] field, not 2113554. I looks like the books online entry might be out-of-date because the field that is labeled [reserved1] in my books online page, is labeled [StatVersion] in my actual [sysindexes] table. That [StatVersion] field looks suspiciously like a Status Version field, possibly indicating that the Status field has undergone some sort of version revision? Is anyone familiar with this stuff? Thanks. Ken
View Replies !
Question On Primary Key, Unique Index
Question: I have a test table like this CREATE TABLE [dbo].[Test]( [name] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL, [addr] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL, CONSTRAINT [PK_Test] PRIMARY KEY CLUSTERED ( [name] ASC )WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] But when I tried to input my data like 'abc', '123 abc' 'abc ','123 abc' SQL server won't recognize 'abc' and 'abc ' is a different value if the last character is a space. Is there a way to make it as a different value? I tried to drop the primary and input the data. When I ran a group by the name column, 'abc' show 2 instead of 1. Seems SQL server is trying to ignore the space at the end too. I also noticed unique index have the same problem too. Please help.
View Replies !
Partition Switch And Non Unique Index
I have a table partitioned by month. There are no primary key. There is another table with the same structure that I use to load data for the current month. I can perform partition switch with no problem. I created non unique index on integer field in both tables. After this change partition switch does not work anymore. Here is an error that I am getting: 'ALTER TABLE SWITCH' statement failed. The table 'dbo.test' is partitioned while index 'IX_test' is not partitioned." Any help?
View Replies !
Unique Index Conflict On Insert Into
I get this message when doing an insert into : Msg 2601, Level 14, State 1, Line 1 Cannot insert duplicate key row in object 'dbo.ElementLocalCharacterised' with unique index 'ElementLocalCharacterised_uq'. The statement has been terminated. Now my question is : does this error mean that all inserts are done, despite those where the unique key produced a conflict? Or does it mean that none of the inserts are done (all are rejected because at least one gave a conflict) ? If the second is the case I would like to know if it is possible to execute the insert into in a way that all inserts are done despite those that raise a conflict? Thanks a lot for any suggestion ! Regards, Fabianus my favorit hoster is ASPnix : www.aspnix.com !
View Replies !
Changing Field For Unique Key Index
SQL 2000 Enterprise Manager won’t let me drop a unique key index and move it to another field. I get an error regarding ‘drop index not allowed because of unique key constraint enforcement. Is there anyway to change fields for unique keys in SQL Enterprise Manager? Thanks, Al
View Replies !
Create Unique Index On Multiple Columns
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?
View Replies !
Unique Index Returns Duplicate Rows
We are running the following query, which has a unique index on Table_2 (col1 and sys1), and Column col1 from Table_1 is unique. select top 100 s.*, x.col1 from Table_1 s left outer join Table_2 x on x.col1 = s.col1 and x.sys1 = 'SYSTEM0' Unfortunately this query returns duplicate rows. And every time the result is different But once we dbcc dbreindex the unique index on Table_2, the result will not have any dups. Any ideas? Thanks Steve
View Replies !
How To Return Primary Unique Index Key On Insert
Hi, I am making a program in Visual Basic .NET with SQL Server 2000. I have a table "MyTable" with a primary key named "Id". The primary key is 'Create Unique' checked and 'Index' selected. When I insert all the fields required, except "Id" of course, I need the new record's "Id" in my VisualBasic program, but I don't know how... I must do one of them, but don't know how either of them: -Create a trigger on insertion that will send to the user that sended the insert command the "Id" of the record just created. or -get the command in Visual Basic that will send the Insert command with a return field ("Id") Thanks in advance, Sebastien Anselmo
View Replies !
Distinct Vs. Unique Index In Stored Procedure
Hi All, I'm struggling with this one. We have a Street database that can contain multiple entries for one record in our main table. We don't wish to pass duplicate rows back to our end user so I wish to get distinct recno's (the unique identifier for the main table) back from our Street Table. We have a stored procedure that currently uses Select distinct Recno but I think this is slower than if I used a unique index with ignore_dup_key. Problem is that when I alter the stored procedure it looks like the functionality of the LIKE function is somehow altered. For example Like 'King%' fails to return anything but Like 'King St%' does. I suspect it is the fact that the error "Server: Msg 3604, Level 16, State 1, Procedure" is being generated. How do I code around this server side.
View Replies !
Distributed Database Unique Index Creation
Can anyone suggest an appropriate strategy to create unique index's when a distributed database is used. As a bit of a background I have several SQL Server 2005 servers scattered around the country and I am trying to come up with a way to create a unique index i.e. one that hasn't been used in a particular table say Staff in any of the databases around the country. Performance is an issue particularly as our network isn't that fantastic has anyone done something similar and achieved reasonable speeds? Thanks ...
View Replies !
Unique INDEX With Upper/lower Case
Hi I can't create unique index like that: 1 - create table abc ( colZ varchar(10) ) 2 - insert into abc values ( "test") 3 - insert into abc values ("Test") 4 - create unique index idx_abc on abc ( colZ ) -- This doesnt work .... duplicate key was found for object name abc...... Is not there difference between "Test" and "test"? Can I work around this? cheers,
View Replies !
Gracefully Handle Unique Index Constraint
I am creating a new SSIS package where there is a flat file (CSV) I am importing. Well, there will be an end-user using a web UI to initiate the import of the file. However, this flat file is generated from another company and then uploaded to our network. This flat file has the potential to have duplicate rows that would have already been imported at a previous date. With the constraints in the table, we have guaranteed that there will not be duplicates added, but the SSIS package fails immediately upon attempting to insert a duplicate row - and the bulk update is rolled back. What I need to be able to do is load all of the rows that are not duplicates into the table. I am guessing that with my current approach, this is not possible. I am using a Data Flow task that converts the data in the flat file and then performs a bulk copy to load the data into the table. How can I either ignore duplicate rows, or otherwise gracefully handle this data import? - - - - - Will - - - - - http://www.strohlsitedesign.com http://blog.strohlsitedesign.com/ http://skins.strohlsitedesign.com/
View Replies !
BULK INSERT Ignores UNIQUE Index With IGNORE_DUP_KEY Set?
All,Just want to make sure that I understand what's going on here.I have a table with IGNORE_DUP_KEY set on a unique, multi-columnindex.What I'm seeing is this:1) When performing a BULK INSERT, the UNIQUE index is not beingrespected and rows which violate the unique index are inserted.2) When performing a regular INSERT, the UNIQUE index is beingrespected and rows which violate the unique index ARE NOT inserted.Is this expected behavior.Also, I have some questions, given the index described.Q1) Will a regular INSERT that attempts to insert duplicate data getan error back or just a warning?Q2) How can I set things up so that a BULK INSERT would NOT allowduplicates to be entered into the table?Thanks,Wes Gamble
View Replies !
Urgent : How To Find Whih Table Has Unique Index
I have many databses and we are trying to see how many can be replicated. Found out 90 on't have primary keys. Next option, find unique indexed tables and convert them to PKeys. Now my question is how do I fin that a table has a unique index and column has "allow null" Please remember I am not asking to find Unique constraint. In other words , folowing query select TABLE_NAME from INFORMATION_SCHEMA.TABLE_CONSTRAINTS where CONSTRAINT_TYPE in('UNIQUE') won't tell you if a table has a unique index. Please help.
View Replies !
Merge Replication: Unique Index Error 1505
We are currently attempting to (Merge) replicate a database between 2 servers; distributor and subscriber. Apparently there are no Primary Keys in the database, and without the input of the developers, we can only assume that data integrity must be maintained by the application and the database is somewhat generic. In any case, what we are experiencing is that when we attempt to replicate, and you may know, you need PKs. Without any, SQL Server has utilized it's own (Row GUIDs). The Snapshot worked fine, but the merge agent reports error 1505. The error is "CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID %d. Most significant primary key is '%S_KEY'." The fix and workaround are SP2 and deselecting Unique in the index propoerties of the index raising the error. We already had SP2 on the system. Tried the other and still the same issue occurs. Microsoft's Knowledgebase has nothing. Any suggestions or fixes would be greatly appreciated. Regards, Alex Cohen (MCSE, MCDBA, MCT)
View Replies !
How To Select Unique Row When Entire Row Not Unique?
I am having a problem trying to figure out the best way to get the results I need. I have a table of part numbers that is joined with a table of notes. The table of notes is specific to the part number and user. A row in the notes table is only created if the user has entered notes on that part number. I need to create a search that grabs all matches on a keyword and returns the records. The problem is that it currently returns a row from the parts table with no notes and a separate row with the notes included if they had created an entry. It seems like this should be easy but it eludes me today. Here is the code Code Snippet create procedure SearchPartKeyword ( @Keyword varchar(250) = null, @Universal_Id varchar(10) = null ) as select p.PartNumber, p.Description, p.ServiceOrderable, n.MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p inner join notes n on p.PartNumber = n.Identifier where n.Universal_ID = @Universal_ID and p.Description like @Keyword union select p.PartNumber, p.Description, p.ServiceOrderable, '' as MyNotes, p.LargestAssembly, p.DMM, p.Legacy, p.Folder, p.Printer from Parts p where p.Description like @Keyword and the results: PartNo Description SO Notes LA DMM Legacy Folder Printer de90008 MAIN BOARD 1 DGF1 114688 0 0 0 de90008 MAIN BOARD 1 I love this part Really I do DGF1 114688 0 0 0 This could return multiple part numbers and If they have entered notes I want the row with the notes Thank You Dominic Mancl
View Replies !
Cannot Insert Duplicate Key Row In Object 'MSmerge_genhistory' With Unique Index 'unc1MSmerge_genhistory'
I have 1 client who keeps running into the following error on the subscriber and merge agents > €œCannot insert duplicate key row in object 'MSmerge_genhistory' with unique index 'unc1MSmerge_genhistory'.€? Last time we got this error I ran a reindex on table MSmerge_genhistory on the publisher database, I then successfully generated a new snapshot and the subscribers started to synchronize again. This time around I keep getting the error even after I follow these steps (I also ran all the jobs to clean up replication). The last time I ran into this error I created a job to reindex msmerge_genhistory on a nightly bases in an effort to avoid this problem. Can somebody please provide me with a workaround and also the reason why this error occurs in the first place. Thank you in advanced, Pauly C
View Replies !
SQL Server 2005; Unique Constraint, Covering Index Question
Hi, When I create a unique constraint, SQL Server automatically creates an index on this constraint. So when I run the following... ALTER TABLE PersonsProjects WITH NOCHECK ADD CONSTRAINT NoDupes UNIQUE NONCLUSTERED (PersonID, ProjectID) ...SQL Server will create a composite index on PersonsProjects called NoDupes on PersonIDand ProjectID. Thing is, I need this index to include a third column Status since most queries use this column in conjunction with PersonID and ProjectID. If there was no index on this table, I would have created it as follows: CREATE UNIQUE INDEX NoDupes ON PersonsProjects (PersonID, ProjectID) INCLUDE (Status) WITH IGNORE_DUP_KEY But this won't enforce the unique constraint on PersonID and ProjectID when performing inserts and updates. Is there any way of creating a unique constraint with an included column? I would rather not have two indexes... NoDupes: PersonID,ProjectID New Index: PersonID,ProjectID INCLUDE Status ...so I'm trying to determine what other options that might be available...please advise. Thanks much.
View Replies !
Cannot Insert Duplicate Key Row In Object 'dbo.lastlogin' With Unique Index 'IX_lastlogin'.
Hi. I have been recently redesigning my tables - creating FK relationships from child tables to the PK userid in the Users table. The specifics of what I did and why can be seen here: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1968856&SiteID=1 But, now I am getting the below error: Cannot insert duplicate key row in object 'dbo.lastlogin' with unique index 'IX_lastlogin'. The statement has been terminated. Or, for that matter, SavedSearches or any other table where I need to insert the same userid twice. I can see why I would want to avoid duplicates in the Users table. But, for lastlogin, savedsearches, and a few of my other tables, the same user may account for multiple rows. Any suggestions as to where I messed up and how to deal with this? Thanks. DBO.USERS Code Snippet CREATE TABLE [dbo].[users]( [userid] [int] IDENTITY(1,1) NOT NULL, [lastname] [varchar](50) NULL, [firstname] [varchar](50) NULL, [email] [varchar](50) NOT NULL, [alternateemail] [varchar](50) NULL, [password] [varchar](50) NOT NULL, [role] [varchar](10) NOT NULL, [securityquestion] [varchar](50) NOT NULL, [securityanswer] [varchar](50) NOT NULL, [zipcode] [int] NOT NULL, [birthmonth] [tinyint] NOT NULL, [birthday] [tinyint] NOT NULL, [birthyear] [int] NOT NULL, [gender] [varchar](10) NULL, [city] [varchar](50) NULL, [state] [varchar](50) NULL, [country] [varchar](50) NULL, [registerdate] [datetime] NOT NULL, [editdate] [datetime] NULL, [confirmed] [bit] NULL CONSTRAINT [DF__Users__confirmed__4CC05EF3] DEFAULT ((0)), CONSTRAINT [PK_users] PRIMARY KEY CLUSTERED ( [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_email] UNIQUE NONCLUSTERED ( [email] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF /****** Object: Table [dbo].[lastlogin] Script Date: 08/22/2007 14:16:16 ******/ SET ANSI_NULLS ON DBO.SAVEDSEARCHES CREATE TABLE [dbo].[savedsearches]( [savedsearchesid] [int] IDENTITY(1,1) NOT NULL, [searchname] [varchar](50) NOT NULL, [userid] [int] NOT NULL, [date] [datetime] NULL, [isdefault] [bit] NULL, [gender] [char](10) NULL, [startyear] [varchar](50) NULL, [endyear] [varchar](50) NULL, [country] [varchar](50) NULL, [miles] [int] NULL, [pictures] [varchar](50) NULL, [postal] [int] NULL, [sort] [tinyint] NULL, [photostring] [varchar](50) NULL, [orderby] [tinyint] NULL, CONSTRAINT [PK_SavedSearches] PRIMARY KEY CLUSTERED ( [userid] ASC, [searchname] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO SET ANSI_PADDING OFF GO ALTER TABLE [dbo].[savedsearches] WITH NOCHECK ADD CONSTRAINT [FK_savedsearches_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[savedsearches] CHECK CONSTRAINT [FK_savedsearches_users] GO SET QUOTED_IDENTIFIER ON GO The following insert statement returned the error in the subject because userid = 32 already exists in the Users table. INSERT INTO lastlogin VALUES (32, CONVERT(VARCHAR(26), GETDATE(), 109), 1, CONVERT(VARCHAR(26), GETDATE(), 109)) DBO.LASTLOGIN Code Snippet CREATE TABLE [dbo].[lastlogin]( [lastloginid] [int] IDENTITY(1,1) NOT NULL, [userid] [int] NOT NULL, [date] [datetime] NOT NULL, [status] [bit] NOT NULL CONSTRAINT [DF_lastlogin_status] DEFAULT ((0)), [activity] [datetime] NOT NULL CONSTRAINT [DF_lastlogin_activity] DEFAULT (getutcdate()), [online] AS (case when [status]=(1) AND datediff(minute,[activity],getutcdate())<(30) then (1) else (0) end), CONSTRAINT [PK_lastlogin] PRIMARY KEY CLUSTERED ( [date] ASC, [userid] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] GO ALTER TABLE [dbo].[lastlogin] WITH NOCHECK ADD CONSTRAINT [FK_lastlogin_users] FOREIGN KEY([userid]) REFERENCES [dbo].[users] ([userid]) GO ALTER TABLE [dbo].[lastlogin] CHECK CONSTRAINT [FK_lastlogin_users]
View Replies !
|
TRACKER
