Difference Between A Primary Key And A Unique Key?
Jan 19, 2008
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 1 Replies
ADVERTISEMENT
Sep 22, 2004
A UNIQUE INDEX must inherently impose a unique constraint and a UNIQUE CONSTRAINT is most likely implemented via a UNIQUE INDEX. So what is the difference? When you create in Enterprise Manager you must select one or the other.
View 8 Replies
View Related
Jun 24, 2006
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 1 Replies
View Related
Nov 13, 2007
What 's difference between Unique key and unique index in SQL server 2005?
View 9 Replies
View Related
Jul 23, 2005
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 4 Replies
View Related
Oct 20, 2006
Hello
for MS SQL 2000 I want to CREATE a Table as follow :
CREATE TABLE [dbo].[Local] (
[id_Local] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar] (100) NOT NULL,
[Info] [nvarchar] (100) NULL
)
id_Local is PrimaryKey autoincrement
but Name must be UNIQUE (not 2 times the same name)
how can script it ?
thank you
View 2 Replies
View Related
Aug 27, 2007
Hi,
Can anyone tell what are the specific scenarios where Unique key is recommened over primary key ?
While designing a database table in what all cases we should think about going for Unique key rather than a primary key.
Regards,
Amit
View 6 Replies
View Related
Aug 10, 2015
What is the difference between the Index and the Primary Key?
View 14 Replies
View Related
Aug 17, 2007
Is there any way to auto generate the unique primary key when inserting data in MS SQL?
there is a way to create the increasing integer as primary key, but is there any ways other than that?
thank you
View 5 Replies
View Related
Jan 23, 2008
For our database application we get our primary keys by calling a stored procedure that pass's in the table name and outputs the next primary key number assigned to that table.
ALTER procedure [dbo].[sp_getNextKey]
@TableName char(100),
@NextKey T_ID output
as
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
begin transaction
select @NextKey = NextKeyValue from T_KeyGenerator where TableName like @TableName
update T_KeyGenerator set NextKeyValue = NextKeyValue + 1 where TableName like @TableName
commit transaction
I then take that primary key, and other data, and insert that into the desired table.
The Problem I am having is that my user's keep getting "cannot insert duplicate key in table".
So I assume that I do not have the transaction set right, or missing something.
I need it to lock the row in the t_keyGenerator table so that no other users can view that row until I update it with the new value and commit the transaction.
Any help would be greatly appreciated
View 8 Replies
View Related
Nov 9, 2006
I wrote this stored procedure and it works fine, it seems. The questions I have are as follows:
(1) What is the difference between PRIMARY KEY and UNIQUE. They seem to pertain to the same behavior. When I used only UNIQUE as qualifier I did not see that the column was marked as primary in the SQL Management Studio.
What I need is for a column to be unique in the sense that it would not allow duplicate values and it must have an INDEX on it. I need it to be descending.
(2) Did I do it right or there are aspects in here I do not quite see?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER OFF
GO
ALTER PROCEDURE [dbo].[CreateTableDailyClose]
@symbol varchar (10) = null
AS
BEGIN
SET NOCOUNT ON;
DECLARE @SQL VARCHAR(500)
SET @SQL = 'CREATE TABLE dbo.dailyCl_' + @symbol + ' (
dateTimed DateTime NOT NULL PRIMARY KEY,
opened float NULL,
high float NULL,
low float NULL,
closed float NULL,
volume int NULL,
adjClosed float NULL
)'
EXEC sp_sqlexec @Sql
SET @SQL = 'CREATE UNIQUE INDEX dateTimed ON dbo.dailyCl_' +
@symbol + ' (dateTimed DESC) '
EXEC sp_sqlexec @Sql
END
Thanks.
View 16 Replies
View Related
Apr 10, 2006
Hi all..!If I want to split an SQL DB into several physical files (as its 500GBdisk ran out of space, won't even run shrinks any more, and we boughtanother 500GB disk to add to the PC)then what is the difference between:Adding another File to the primary group which will reside on the newgroup;Adding another file in another group.We do not want to set any db objects (Tables, indexes)to a secondary file, as this will involve lengthy data movingoperations. We would like the DB to continue working from where it isutilizing the added space in a contigous (striped) manner.Will striping occur in both cases? as I understand striping it meansthat our stuck SQL Server will awake back to life as it will now have500GB more data for its DB, even though we haven't set any of itsobjects (tables, indexes) to explicitly use the secondary NDF file onthe new disk?or will it only utilize the new space if we set some objects to resideon that NDF?for example if we run large queries which crash now (due to lack ofspace) when we add the second drive will they start to work as theprocess will grow striped from the full drive to the new drive, even ifall the queries' source tables are all still set to the old drive?Thanks for any replies?
View 1 Replies
View Related
Feb 23, 2006
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 5 Replies
View Related
Oct 8, 2007
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 5 Replies
View Related
Oct 13, 2015
What is the need of Primary Key if everything can be achieved by Unique Key? Everything that primary key does, Unique key can also fill those things.Why primary key is required?
View 4 Replies
View Related
May 27, 2002
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 6 Replies
View Related
Jan 25, 2006
I have a deal table, each of these investments must be unique. I created a int pk : idDeal. Does that make sense or should i just use the deal colm being it has a unique constraint,
Reguarding indexes, should i make the auto # colm my pk and make that the clustered index? and put another index on the Deal Colmn? Any suggestions welcomed
Thank you
View 4 Replies
View Related
Mar 21, 2008
Hi
I have a strange requirement in ETL operation. My Source contains only the details table data. Out of it, I have to load the master table and refer the Master table primary key ID to load Details table. I can easily load the Master Table with aggregate transformation. But problem is how to look up the Master Table Primary Key ID to load the Details table, as the master does not contains any unique key to lookup. This may seems to be strange but this is my requirement. You can refer the Source and Destination data model as below. They may give you clear picture. Can you guys help me out on this?
My Source Table
--------------------------
Citye Type
City Name
Source
New York
Via City1
Pittsburg
Destination City
Chicago
Source
New York
Via City1
Philadelphia
Destination City
Chicago
My Destination
------------------------
Master Table
ID
1
2
Detail Table
Master Table ID
Citye Type
City Name
1
Source
New York
1
Via City1
Pittsburg
1
Destination City
Chicago
2
Source
New York
2
Via City1
Philadelphia
2
Destination City
Chicago
Thanks
View 11 Replies
View Related
Apr 7, 2008
Hi,
Te following situtation is :
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?
View 2 Replies
View Related
Jul 19, 2013
I have created two tables. table one has the following fields,
Id -> unique clustered index.
table two has the following fields,
Tid -> unique clustered index
Id -> foreign key of table one(id).
Now I have created primary key for the table one column 'id'. It's created as "nonclustered, unique, primary key located on PRIMARY". Primary key create clustered index default. since unique clustered index existed in table one, it has created "Nonclustered primary key".
My Question is, What is the difference between "clustered, unique, primary key" and "nonclustered, unique, primary key"? Is there any performance impact between these?
View 5 Replies
View Related
Sep 4, 2015
We are going to use SQL Sever change tracking. The problem is that some of our tables, which are to be tracked, have no primary keys. There are only unique clustered indexes. The question is what is the best way to turn on change tracking for these tables in our circumstances.
View 4 Replies
View Related
Feb 20, 2007
Hello,
I have a table which has a composite primary key consisting of four columns, one of them being a datetime called Day.
The nice thing afaik with this composite key is that it prevents duplicate entries in the table for any given day. But the problem is probably two-fold
1. multiple columns need to be used for joins and I think this might degrade performance?
2. in client applications such as asp.net these primary keys must be sent in the query string and the query string becomes long and a little bit unmanagable.
A possible solutions I'm thinking of is dropping the existing primary key and creating a new identity column and a composite unique index on the columns from the existing composite key.
I would like to have some tips, recommendations and alternatives for what I should do in this case.
View 1 Replies
View Related
Nov 6, 2014
I have following script which i am planning to run to drop all non-clustered primary keys on a database and then created as clustered. I am using someone else's script so don't know how to modify this. Some of primary key columns are used in references in other tables.
is there anyway i can drop the existing primary keys and using their original script then create again as clustered including restoring all foreign and reference keys and unique or no unique.
DECLARE @table NVARCHAR(512), @tablename NVARCHAR(512),
@sql NVARCHAR(MAX), @sql2 NVARCHAR(MAX), @sql3 NVARCHAR(MAX),
@column NVARCHAR(MAX);
DECLARE @indexname NVARCHAR(512);
SELECT name As 'Table'
[Code] ....
View 8 Replies
View Related
Oct 24, 2007
Question is in the subject.
Thanks in advance
-Jamie
View 7 Replies
View Related
Jul 5, 2015
This index is not unique
ix_report_history_creative_id
Msg 2601, Level 14, State 1, Procedure DFP_report_load, Line 161
Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'.
The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
Msg 3621, Level 0, State 0, Procedure DFP_report_load, Line 161
The statement has been terminated.
Exception in Task: Cannot insert duplicate key row in object 'dbo.DFP_Reports_History' with unique index 'ix_report_history_creative_id'. The duplicate key value is (40736326382, 1, 2015-07-03, 67618862, 355324).
The statement has been terminated.
View 6 Replies
View Related
Jul 20, 2005
HelloWhat should I use for better perfomance sinceunique constraint always use index ?ThanksKamil
View 5 Replies
View Related
Mar 7, 2001
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 4 Replies
View Related
Jan 20, 2006
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 2 Replies
View Related
Mar 26, 2008
hi team,
.Can i create umique constraint with out unique index.when i am creating a unique constraint sql creates a unique index (default) can i have only unique constraint ?
View 12 Replies
View Related
Mar 12, 2008
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 1 Replies
View Related
Jan 11, 2007
Uma writes "Hi Dear,
I have A Table , Which Primary key consists of 6 columns.
total Number of Columns in the table are 16. Now i Want to Convert my Composite Primary key into simple primary key.there are already 2200 records in the table and no referential integrity (foriegn key ) exist.
may i convert Composite Primary key into simple primary key in thr table like this.
Thanks,
Uma"
View 1 Replies
View Related
Aug 28, 2002
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
View 9 Replies
View Related
May 1, 2008
Here is the table I created:
create table Test (
[recId] [int] identity(1, 1) not null,
[code] [varchar](50) not null,
[prime] [bit] not null constraint [DF_Test_prime] default (cast(0 as bit)),
constraint [PK_Test] primary key clustered
(
[recId]
) with fillfactor = 90 on [primary]
) on [primary]
go
insert into Test (code, prime) values ('AVA', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(1 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('BUS', cast(0 as bit))
insert into Test (code, prime) values ('CAR', cast(1 as bit))
insert into Test (code, prime) values ('CAR', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(1 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
insert into Test (code, prime) values ('RLW', cast(0 as bit))
select *
from Test
I need to create a constraint on this table that will not allow me to have two rows that are prime for the same code. So the following insert statement should fail:
-- This should fail
insert into Test (code, prime) values ('RLW', cast(1 as bit))
Thanks for you help!
Regards,
Anand
View 11 Replies
View Related
