Creating A Primary Key Column In SQL Server 2005
Feb 1, 2008
I have a varchar column in a table, which is an unique key column. Now when I design this table should I use this column as a primary key or should I add one more Integer column as a primary key column for the performance?
One more point is, when ever I do a search on this table I will search based on that unique varchar column values only, so even then if I add new integer column just for Primary key I will not use this column for searching values.
With these information, can some body help me in deciding this?
View 2 Replies
ADVERTISEMENT
Mar 11, 2008
I currently have a website which is using ASP.NET 2.0, C#, and SQLServer 2005. The website will be used to enter grants for auniversity. When a new grant is entered, I need to generate a primarykey. The primary key will need to follow the format: Two digit forfiscal year, then number of the grant for that year. Example:Year 08 and 14th grant of the year would be: 0814How can I implement this. Right now, I have a "New Grant.aspx" pagewith a Submit button. I am guessing the date is going to be formattedin C#. How can I check what the last primary key in the database is?Also, it seems to me that SQL Server insists that the primary key be32 bits long, however my primary key will only be 4. How can Ioverride this? Thanks.
View 3 Replies
View Related
Feb 29, 2008
Hello,
I am trying to recreate a primary key that I dropped in a table....
I'm using a statement like
ALTER TABLE temp WITH NOCHECK add CONSTRAINT PK__tempkey PRIMARY KEY CLUSTERED
(
num,
store
)
But apparently the "store" column was created without a "not null" and it let it create the PK during the table creation but now it won't let me add the PK with that nullable column..
Does anyone know how to get it to use that column to create a primary key after the initial table creation?
Here is the error i get
Cannot define PRIMARY KEY constraint on nullable column in table
Thanks a lot
View 5 Replies
View Related
Jul 23, 2005
This is a fairly simple question, but what is the easiest way to:create a new numbered column (where value is simply the row number) inan existing table and setting it as a primary key?
View 1 Replies
View Related
Mar 27, 2008
Hello everyone!
Even though I am not "new to SQL Server", my experience in working with it has ebbed and flowed, and working with SQL Server is not something that happens for me even on a monthly basis.
I work as an in-house programmer for a financial investment company and I have been asked to work on a database that is to hold financial data.
One of the requirements for the tables has been that the Primary Key should be [name_of_the_table] + [Identity].
So, for example, for table "tblEquities", the values of the Primary Key would be:
PK
-------
tblEquities1
tblEquities2
tblEquities3
tblEquities4
tblEquities5
My questions are:
1) Is it possible to achieve this by using just one column, as in defining a formula for the "Computed Column Specification" , something that would look like "'tblEquities' + Identity()"?
2) If the above is not possible, I assume it's much easier to just define a column as being Identity column and then create a 2nd computed column that would aggregate the name of the table with the value in the Identity column, so that I end up with a column holding the desired values? (I guess it would look something like
"'tblEquities' + [Identity]")
Also, I have been asked to find out what SQL 2005 have/offers, in term of guaranteeing referential integrity. Are the "Database Diagram", as well as the "Relationships" dialog box, the tools that would meet that criteria or does SQL 2005 contain other tools that would help in this regard?
Thank you!
View 6 Replies
View Related
Jun 26, 2014
Discuss the following sql query with respect to performance in an applicaiton involving more number of concurrent users creating and deleting records. The objective is to create continuous primary key integer values.
Table name: SitePage
Column DataType
--------- -----------
PageID BigINT
PageName nchar(10)
Query to insert new record
DECLARE @intFlag INT
SET @intFlag = 0
WHILE (@intFlag =0)
BEGIN
BEGIN TRY
[Code] ....
We don't want to use auto increment integer value for primary key because of the following reason
[URL] .....
We also don't want to use SEQUENCE as we have to create 50 sequence for 50 tables
We can't do trace flag 272
View 9 Replies
View Related
Apr 30, 2015
Environment: Microsoft SQL Server Standard Edition (64-bit), 10.0.5520.0
I was doing a code review for another developer and came across this code:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
[Code] ....
This EXECUTES with no error or warning message.However, if I change this to CREATE the PK in an ALTER TABLE statement, I get the (expected by me) error:
CREATE TABLE dbo.#ABC
(
ReportRunTime DATETIME
,SourceID VARCHAR(3)
,VisitID VARCHAR(30)
,BaseID VARCHAR(25)
,OccurrenceSeqID INT
[code]...
==> Msg 8111, Level 16, State 1, Line 17 Cannot define PRIMARY KEY constraint on nullable column in table '#ABC'.
==> Msg 1750, Level 16, State 0, Line 17 Could not create constraint. See previous errors.
(note: As the #ABC table is an actual copy of a few of the columns in a "permanent" table, I will likely change the definition as follows such that the columns are defined to match the names / datatypes / NULLability:
SELECT TOP 0
CAST('01-01-1980' AS DATETIME) AS [ReportRunTime]
,SourceID
,VisitID
,BaseID
,OccurrenceSeqID
[Code] .....
View 9 Replies
View Related
Oct 25, 2007
Update: Changed original post as I figured it was not the problem I stated.
Hi all,
Heres the problem. I have a table in SQL Server with two fields, PartSubCategory and PartCatID. PartSubCategory is the primary key. When I import an excel sheet with identical columns into this table, the primary key column in the table gets sorted automatically (alphabetically). I don't want this column sorted but to import the rows from excel in the order as they were. Is this some indexing issue? Is there a fix?
Thanks a lot...Bullpit
View 13 Replies
View Related
Oct 19, 2015
How to add a primary key for existing column in the table
View 8 Replies
View Related
Jun 3, 2008
Completely new to SQL so wondered if anyone could help with an issue I'm sure there is a simple answer too.
Need to create a Primary Key for an existing table. Now I have managed to create a new column called _ID, which set the contents to NULL. So populated the column with the number 1 so that I could set it to no null.
Now when I run the query:
ALTER TABLE tablename ADD PRIMARY KEY (_ID);
I get the error:
The CREATE UNIQUE INDEX statement terminated because a duplicate key was found for the object name 'dbo._DATACAPTURE' and the index name 'PK___DATACAPTURE__145C0A3F'. The duplicate key value is (1).
Which is obviously due to the repeated 1 in _ID.
So I am thinking I just need to run a query that fills the _ID with a unique number.
Any ideas how I do that?
Thanks for your help in advance.
View 5 Replies
View Related
Feb 2, 2004
Hi
I'm trying to create a composite Primary Key on a table. This is the SQL I've written:
CREATE TABLE BookingItems
(
BookingID INT NOT NULL
REFERENCES Bookings(BookingID),
EquipmentTypeID INT NOT NULL
REFERENCES EquipmentType(EquipmentTypeID),
CONSTRAINT PK_BookingItems_id PRIMARY KEY
(BookingID, EquipmentTypeID)
)
Is this right? I'm trying to define a Primary Key made up of BookingID and EquipmentTypesID, which are both Foreign Keys as defined in the column definition.
Thanks
Jon
View 1 Replies
View Related
Aug 19, 2002
Thanks to help from Ray Maio I have been able to create copies of various tables using the Select Into command.
Ray also responded that this command does does not transfer Primary Key and Index information.
I have attempted to create an index by using the following statement:
create nonclustered index SchoolIndx on wrestlerstest (School)
I did not get an error, but can't tell ift the index was created. The question is, how do I find out if
there is an index for the column School? Is there a command that will tell me. I'm just sending commands
to the server via a short .asp program and don't really know how to obtain a message back. Could I find the information
by browsing the
Thanks again in advance,
Greg Zafros
View 1 Replies
View Related
Jun 29, 2006
Hi,
I need to create a new table in our database.
This table is not linked into the existing schema in anyway, so i'm not sure if I need a primary key or not.
either way, coudl anyone tell me how to create a primary key ni the CREATE TABLE statement.
I have tried searching but cannot find the answer.
many thanks,
Matt
View 7 Replies
View Related
Oct 17, 2005
Hi all,I've spent hours trying to find the error in the following SQL 2000command:ALTER TABLE [ClientList] ADD CONSTRAINT [PK_ClientList] PRIMARY KEYCLUSTERED ([PhoneNo]) On [PRIMARY]Every time I try to "Execute" this from my (VB5) ODBC connection I get:Runtime error 3289;Syntax error in CONSTRAINT clauseFor the life of me I can see nothing wrong. I used Enterprise managerto create this statement, and I can create the primary key fine fromthere. The PhoneNo field does not allow NULLs.Everything is service-packed up to date. I have tried using[databasename].[dbo].[ClientList] and suchlike.I've tried to find a relevant manual, but my SQL Server developersguide suggests this should be ok and I can't see anything wrong in thebooks online.Can anybody please help?TIA
View 7 Replies
View Related
Sep 17, 2007
I'd like to create a primary key value (incremental) within a trigger and set it in a primary key column.
Any idea anyone? Do I define my trigger as a On INSERT, Instead of INSERT? I tried both but it doesn't seem I'm doing things right.
View 3 Replies
View Related
Jul 25, 2007
Hello, How are you?
I have a problem when I try to create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE. When I€™m going to choose the data source, the SQL Server 2005 Compact Edition provider doesn€™t appear in the list. I installed the SQL Server 2005 Compact Edition from this page http://www.microsoft.com/downloads/details.aspx?FamilyId=%2085E0C3CE-3FA1-453A-8CE9-AF6CA20946C3&displaylang=en. In the additional information says by installing SQLServerCE31-EN.msi installs the provider (System.Data.SqlServerCe.dll) in the GAC (global assembly cache) and registers the OLEDB provider (sqlceoledb30.dll). So, I don€™t understand why couldn€™t I create a new connection with Microsoft SQL Server 2005 Compact Edition from Visual Studio 2005 IDE?
I appreciate your help€¦
View 6 Replies
View Related
Jul 12, 2004
This question may be a little complicated.
I am building a DTS Package that is moving data from our webstore (written in house) to a Warehouse Management System(WMS - Turnkey) and I've encountered a problem. both pieces of software have an orders table and an Ordered_Items table, related by the order_ID (makes sense so far). Here is the problem. The primary key on the webstore's Ordered_Items table is a single column (basically an Identity variable), while the primary key on the WMS's Ordered_Items table is a dual column primary key, between the Order_ID and the Order_LineID, so the data should be stored like:
OrderID Order_LineID
1 1
2 1
2 2
2 3
3 1
3 2
4 1
Get the Idea? So I have to create this new Order_LineID column. How can I accomplish this with a SQL statement?
Thanks!!!!!
View 5 Replies
View Related
May 30, 2000
I created table and also I define the primary key its okay
but when I generate the SQL script for that table its not
creating the primary key
CREATE TABLE [dbo].[table1] (
[emp_id] [int] NOT NULL ,
[emp_name] [char] (25) NULL ,
[emp_address] [nvarchar] (50) NULL
) ON [PRIMARY]
GO
PS: I want to use emp_id as primary key but its not defined in the sql script
Thankx a lot
View 3 Replies
View Related
Jul 18, 2007
Hi,
I have created a very simple table. Here is the script:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[IndexTable]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[IndexTable]
GO
CREATE TABLE [dbo].[IndexTable] (
[Id] [int] NOT NULL ,
[Code] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
) ON [PRIMARY]
GO
CREATE CLUSTERED INDEX [CusteredOnCode] ON [dbo].[IndexTable]([Id]) ON [PRIMARY]
GO
ALTER TABLE [dbo].[IndexTable] ADD
CONSTRAINT [PrimaryKeyOnId] PRIMARY KEY NONCLUSTERED
(
[Id]
) ON [PRIMARY]
GO
The records that i added are:
Id Code
1 a
2 b
3 aa
4 bb
Now when i query like
Select * from IndexTable
I expect the results as:
Id Code
1 a
3 aa
2 b
4 bb
as i have the clustered index on column Code.
But i m getting the results as:
Id Code
1 a
2 b
3 aa
4 bb
as per the primary key order that is a non clustered index.
Can anyone explain why it is happening?
Thanks
Nitin
View 3 Replies
View Related
Sep 30, 2014
We have a database where many tables have a field that has to be lengthened. In some cases this is a primary key or part of a primary key. The table in question is:-
/****** Object: Table [dbo].[DTb_HWSQueueMonthEnd] Script Date: 09/25/2014 14:05:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[DTb_HWSQueueMonthEnd](
[Code] ....
The script I am using is
DECLARE@Column varchar(100)--The name of the column to change
DECLARE@size varchar(5)--The new size of the column
DECLARE @TSQL varchar(255)--Contains the code to be executed
DECLARE @Object varchar(50)--Holds the name of the table
DECLARE @dropc varchar(255)-- Drop constraint script
[Code] ....
When I the the script I get the error message Could not create constraint. See previous errors.
Looking at the strings I build
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] DROP CONSTRAINT PK_DTb_HWSQueueMonthEnd
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] Alter Column [Patient System Number] varchar(10)
ALTER TABLE [dbo].[DTb_HWSQueueMonthEnd] ADD CONSTRAINT PK_DTb_HWSQueueMonthEnd PRIMARY KEY NONCLUSTERED ([Patient System Number] ASC,[Episode Number] ASC,[CensusDate] ASC)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
They all seem fine except the last one which returns the error
Msg 8111, Level 16, State 1, Line 1
Cannot define PRIMARY KEY constraint on nullable column in table 'DTb_HWSQueueMonthEnd'.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.
None of the fields I try to create the key on are nullable.
View 2 Replies
View Related
Jul 16, 2007
Hey everyone,
I have just started getting into to SQL and am completely brand new to the whole concepts of relational databases. Someone on this forum pointed to the MSDN videos on LEARNVISUALSTUDIO.NET which have been very helpful. Unfortunately while learning about relational databases and looking at the program that I want to design and make using them, I have run into a pretty big wall, concerning the primary key and foreign key.
For my program, I am trying to save an object, and lets say the base class is SLIDE. Now SLIDE will store basically most of the information that I will ever want to store, such as timeCreated and mainText and slideID(primarykey). But there are other classes that derive from slide that will store just a bit more information than that because of what they are. Lets say there is a class derived from SLIDE called PERSON that stores its parentNode, which is to say something that this slide specifically belongs to and has a reference to. Now the tricky part is that in this program, every single slide can have a reference to another slide, that you would see displayed and that you could just click on and view if you wanted to.
Now relating what I just told about the classes in my program to a relation database standpoint is what confuses me. If I make a table SLIDE, it would hold incomplete data about the PERSON object, because that object has more data than SLIDE has. The answer to this was to make another table called PERSON, which would have more columns. But now we arrive at the big problem: The primary key called maybe SLIDEID would be different in this new PERSON table than in the other table called SLIDE (or any other derived class). Therefore the link between these tables is messed up. In my object orientated mind I am thinking of static class variables that would still stay constant for derived classes, so that when I make a PERSON slide it will increment off of the primary key of the SLIDE table. In other words, is there some sort of super TABLE that you can derive from, like an abstract class, where the primary keys of other tables can build off of because they will be used as the same type of reference to eachother.
If none of this made sense to the reader, I am greatly sorry. I do not really know what else I can say to convey to you the problem I have. Maybe its because I am so used to object orientated languages that this is making it so difficult to explain. If however you do understand what I am talking about, please think about it and help me find a solution to this problem. I am not an experienced programmer, but I do very much enjoy it and I am very excited about starting to make this program, and I have learned that before I start coding it is very important to have a very firm design in mind first.
Thank your for reading,
Jeremy
View 5 Replies
View Related
Sep 13, 2007
Hi,
Please, What s the differnce between a column that s a primary key and a column that s a "key/index with Isunique=true"?
Thanks a lot.
View 4 Replies
View Related
Aug 15, 2007
Running [dbo].[insertlogin] ( @log = hiten, @pas = hiten ).A .NET Framework error occurred during execution of user-defined routine or aggregate "insertlogin": System.Data.SqlClient.SqlException: Must declare the scalar variable "@Log".System.Data.SqlClient.SqlException: at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnectionSmi.EventSink.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at Microsoft.SqlServer.Server.SmiEventSink_Default.ProcessMessagesAndThrow(Boolean ignoreNonFatalMessages) at System.Data.SqlClient.SqlCommand.RunExecuteNonQuerySmi(Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) at System.Data.SqlClient.SqlCommand.ExecuteNonQuery() at SqlServerProject1.StoredProcedures.insertlogin(SqlString log, SqlString pas).No rows affected.(0 row(s) returned)@RETURN_VALUE = Finished running [dbo].[insertlogin]. ***************************************************************all i am trying to do is : creating a SP in VS using managed code and then trying to execute it. But every time i get the above error. If you can tell me how to edit connection string in this that would be very helpful. At present i am using : Using conn As New SqlConnection("context connection=true") I tried to do "" ALTER ASSEMBLY SqlServerProject1 WITH PERMISSION_SET=EXTERNAL_ACCESS""but i get this error "" Msg 10327, Level 14, State 1, Line 1ALTER ASSEMBLY for assembly 'SqlServerProject1' failed because assembly 'SqlServerProject1' is not authorized for PERMISSION_SET = EXTERNAL_ACCESS. The assembly is authorized when either of the following is true: the database owner (DBO) has EXTERNAL ACCESS ASSEMBLY permission and the database has the TRUSTWORTHY database property on; or the assembly is signed with a certificate or an asymmetric key that has a corresponding login with EXTERNAL ACCESS ASSEMBLY permission. If you have restored or attached this database, make sure the database owner is mapped to the correct login on this server. If not, use sp_changedbowner to fix the problem.""" *********************************************************************Plz help
View 13 Replies
View Related
Oct 24, 2006
In a new instalation of SQL Server 2005. I've made a restore from SQL Server 2000 database.
Solve some problem with the user, and it looks ok.
I'm using a asp web aplication, with vb dll, and change the sql provider from "SQL Server" to "SQL Native Client". It works, but in some recordsets the value a recive for the table primary key is null?????
If execute the querie in the database, the result has values in the primary key.
the same appends when i connect to an SQL Server 2000 using "SQL Native Client".
Please help, what can i do.
View 7 Replies
View Related
Feb 1, 2007
Hi,
I am trying to execute following sql script in sql-server 2000 query analyzer
CREATE TABLE user_courses (user_id varchar(30) NOT NULL PRIMARY KEY,
course_id varchar(10) NOT NULL PRIMARY KEY)
Its give's me following error :-
Cannot specify multiple primary key constraint
Hence I am not able to ceate table with multiple primary keys. So can any one tell me how to get this done?.
Secondly, Primary key must be unique i.e duplicate values are not allowed in P.K field. But in this case since I am declaring two fileds as primary keys.
Will it allow me to have following records in the user_courses table?
user_id(P.K) course_id(P.K)
bob CRS235
alice CRS235
Tim CRS235
tom CRS635
So, if we consider both the fields as primary keys together than I am not voilating Uniqueness constraint. But, if I look at course_id alone then I am voilating uniqeness property?
Thanks,
View 4 Replies
View Related
Aug 15, 2006
Hi All,
I have created a table using VIEWS in SQL server 2005, now i want to be ablle to edit it in a datagrid but i cannot do so as i there is no primary key!
now does anybody know how to set a primary key constraint so i can set one of the fields as a primary key to identify the row?
many thanks
View 3 Replies
View Related
Jun 24, 2006
Hi there,
When the distribution agent runs trying to apply the snapshot at the subscriber I get the following error message
Message
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number:
Message: Batch send failed
2006-06-24 12:41:59.216 Category:NULL
Source: Microsoft SQL Native Client
Number: 2627
Message: Violation of PRIMARY KEY constraint 'MSHREPL_1_PK'. Cannot insert duplicate key in object 'dbo.ITEMTRANSLATION'.
2006-06-24 12:41:59.216 Category:NULL
Source:
Number: 20253
What could possibly cause this error? And how can I possibly fix it?
Best regards,
JB
View 7 Replies
View Related
Sep 6, 2007
Is it possible to create a linked server from an Excel spreadsheet and give it a primary key? If so, how?
Thanks,
--Stan
View 2 Replies
View Related
Nov 19, 2007
Hello I am using SQL Server 2005 I want to delete the data from my table after specific number of days.This should happen automatically. After the days I specified the data from the table should automatically get deleted. Can anyone let me know how to achieve this.If anyone can give an example for this it will be great.Thanks in advance.
View 1 Replies
View Related
Apr 17, 2008
In 2005 the security login will not allow me to add a user, database role, application role or schema manually. If I use a Transact-SQL in Query Editor to create a Login with a password, it said it was successful yet The T-SQL added my login as an application role is this correct? and why can I not add manually? is there another function that must be performed first?
I am very new to the environment but know 2000 very well and miss it being so user friendly.
View 3 Replies
View Related
Jun 23, 2008
Hi,
We wish to create cube in SQL Server 2005,
where we can create a cube using query.
View 2 Replies
View Related
Jan 10, 2007
I am developing an application in VB.NET that is using a sql database. I am trying to start testing with multiple computers running the app while the database is on a remote computer. I have 2 desktop computers networked right now with 1 of them having the database and the other I want to run the program on. I am trying to figure out how to use windows authentication to login to sql server from the remote computer. I can login using Sql authentication but it is taking forever and I keep getting timeout errors so I wanted to try Windows Authentication to see if it is faster. If someone could walk me through the steps I need to take or give me some suggestions to fix my problems I would greatly appreciate it.
View 2 Replies
View Related
Mar 14, 2008
In oracle we create abstract datatypes using the command, for example,
CREATE TYPE address as object
(Street varchar2(50),
cuty varchar2(25));
Can this be done in SQL SERVER 2005? I am new to SQL SERVER 2005. Please help me out giving out the proper syntax alongwith an example.
View 1 Replies
View Related
