Does anyone has an idea on creating a new field in a table with more then say 10000 rows already in that table, and making that new field as primary key field. None of the fields in that table are unique.
View 2 RepliesHey guys.
I just wanted to ask you a question? we are having a field declared
as BIT and we want that field to be unique..We are not able to declare
as a primary key..Is there anyway to declare as unique from the database
point of view?..please help us out in this issue and i'd appreciate that.
Andrew.
Hi,
I want to find out the primary key field name of the table using system table information i.e. sysobjects, syscolumns etc.
I tried to find it out but it seems to be very complicated.
Could anybody help me in this regards?
Thanks in advance.
Prasanna.
Hello,
I have an app that is connected to SQL 2005 express edition.
In the DB there is a table that has an auto incremental primary key
What I want is, I want to get a new value for that auto incremental field before sending the new record's date to the DB to be stored. So I can display the new record's ID in my app's interface
So is there something in SQL server like SEQUENCE in oracle.
Thanks in advance.
I need to insert a row of data and return the value of the primary key id of the row.
I thought that something like this would work
int Key = (int)command.ExecuteScalar();
where command is SqlCommand object.
It doesn't work, maybe I've misunderstood the usage of ExecuteScalar.
I have a table (table1) that has a bunch of fields....[field1][field2][field3][field4][field5]None of these fields are unique, but if I combine them, then they areunique.I know there is a way to make multi-field primary keys, but when I tryI get an that field1 is not unique, which I already know,How can I make a multi-field primary key?thanks
View 2 Replies View RelatedI want to use the Identity field (increment 1,1) as a primary key andhave a unique constraint on my other field which is of type char.I am worried that related data in other tables may lose referntialintegrity if records in the ID table get messed up and need to bere-entered.Can you please advice on best way to do this. I definitely need anumeric id field because it makes the joins and queries so muchfaster.
View 9 Replies View RelatedI was just wondering on a very simple database table with lets say a primary key set to columb ID and another columb lets say products, can you make the primary key automaticly increment its self whenever a new entry has been put in?For instance say I have this table set up with ID Being the primary KEY, Columb 1 = ID( INT ), Columb 2 = Products ( VarChar(50) ), and have the fields ID = 1, and products = my product.....and if a user inserts a new record say from a gridview or some sort of data entry the second ID Feild will automaticly be 2 and the products gets updated per user input.......I'm very sorry but I'm having a hard time putting this into words for some reason..umm basicly user adds something into the products feild and the ID field automaticly increments one number higher from the last one?ThanksAdam.
View 4 Replies View RelatedI'm really puzzled with this. I am new to using MS SQL and need help with the primary key. When I was using Access, I would open the database table and just enter values for the various columns, each time a new column was begun, the computer would insert a unique number there. OK, so now I am using visual studio to work on a SQL database. I created a new table and the first field I specified was "ID", I then chose it as the primary key. For default value, I tried both auto number and auto increment. These don't seem to work, as when I type values into fields and try to move to the next row, having left the ID field blank, I get an error which reads "String or Binary data may be truncated". What is going on, what do I need to do so the field automatically populates with an ID number... Some research sort of led me to the concept of a "trigger" to do this. Is this the approach. I was able to r-click on the table and I saw an option for "create trigger" or "new trigger"... How do I learn to do this, is there a generic trigger to do an auto increment of a primary key ID field?
View 2 Replies View RelatedHi everybody couldn't get through with saving my data on the table with two primary keys...
my table structure is this
pubidintUnchecked (primary key)
pubchar(1)Unchecked
publchar(1)Unchecked
pubcodechar(2)Unchecked (primary key)
a sample data is here
pubid pub publ pubcode
1 a b ab
1 b b bb
2 a b ab
2 b b bb
when i save this table modifying the pubid and pubcode as primary keys the following error displays...
Unable to create index 'PK_PUBS3'.
CREATE UNIQUE INDEX terminated because a duplicate key was found for index ID 1. Most significant primary key is '51'.
Could not create constraint. See previous errors.
The statement has been terminated.
what i understand is that on the primary key duplicates are not allowed how could i allow it?
thanks
Hi,
Does anyone know how should I create a table in order that I can insert values(numbers) in the primary key field, using insert statements. I also would like to know if there are any differences between SQL 2k and SQL 2k5.
Thanks in advance for any reply.
is there any way that i can copy primary key(PK) value to another field in the same table .. say my PK is MemberID i want to replicate that into other field say SortID at the same time when the primary key is incrementing. I'm a newbie in this field please pardon me if something is wrong in the way i'm asking ...please help me friends i'm struggling since a long time ... Thanks in advance ..
savvy
Hello I currently have a merge replication set up with 4 subscribers. A primary field for one of my tables is set to a integer indetity.
What Ive noticed is that depending on which database I enter data into, the indentity field (primary key) is set within a certain range I.e
On Server 1 - Values start from 1 then 2,3,4 etc etc
Server 2 - 24001, 24002, 24003 etc etc
Server 3 - 46001, 46002, 46003 etc etc
Server 4 - 68001, 68002, 68003
My question is what happens when these ranges eventually conflict? Do they automatically gain a different range such as 142 001, 142 002 etc etc?
Ive tried looking in SQL Help, and a quick search here, Im just after some confirmation before I implement this to my app.
cheers
I'm trying to get a SQL 7 and 6.5 DB to interact, but while there is no problem in SQL7, I cannot create a table called "Public" or a field called "Primary"!!
Does anyone know why this might be and if so where I might get a list of any other "invalid" names??
Thanks in advance,
Damon
Trying to get the lowest unused value of CrewID from the below table in a efficient manner. I cant change the table structure as its from a 3rd party and it would break our support agreement.
CREATE TABLE [dbo].[Crews](
[CrewID] [nvarchar](10) NOT NULL,
[CrewName] [nvarchar](200) NOT NULL,
[CompanyID] [nvarchar](10) NOT NULL,
[StartTime] [nvarchar](50) NULL,
[Code] .....
How can i find the primary field name and primary table name of a given foreign key.
For example
Table A:
IDa
Column1
Column2
Column3
Table B:
IDb
column1
column2
column3 <---- this is foreign key to table A's IDa
What i want to do is i pass input of tableB, column3 and i get name of Primary tableA and field name IDa. How is it possible to do in tsql ?
I want to create a table withmember id(primary key for Students,faculty and staff [Tables])and now i want to create issues[Tables] with foreign key as member idbut in references i could not able to pass on reference as orcondition for students, faculty and staff.Thank You,Chirag
View 3 Replies View RelatedHi there,
I have been hired for a couple of weeks to investigate the performance of a sql server 2000 system.
One of the things that strikes me is that all the Primary key (identity field) fileds uses an decimal(18,0) as it's datatype.
An decimal with a precision of 18,0 takes 9 bytes for each column, while an int takes only 4 bytes and and bigint 8 bytes.
Many tables aren't that big, so the values will fit in an int datatype.
1. Is iot a good option to change the decimals columns to an int column ?
2. Many of these columns are indexed by a clustered index. Can the decimal datatype be a performance issue ?
3. sometimes they have deadlocks due page splits. Can this by reduced by changing the data types, while more data fit's into an page?
Thanks in advance,
Greetz,
Patrick de Jong
I have a question on autogenerating numbers for a primary key field, "studyID," in a table€”but with a few twists.
We want studyID to be automatically generated as a 5-digit number. Additionally, we have two study sites and would like the studyIDs pertaining to the first site to begin with a 1 and StudyIDs associated with our second site to start with a 2. When we begin entering data, we will enter either a 1 or 2 in a field called, "Site." Upon entering that 1 or 2, we would like at that moment for Access to instantly autogenerate the appropriate studyID for that site and put it in the "StudyID" field. We want the very first number generated for each site to end in a 1 (10001 and 20001).
Here€™s the range of values we want our StudyIDs to be (this is to be our validation rule as well):
10001-19999 for Site 1
20001-29999 for Site 2
Your suggestions are VERY VERY WELCOME! THANKS!
I am designing a database. I want to define a automatic sequence on a table primary key field. what is the best solution for it?
I know I can enable identity property for a field, but it has some problems ( for example its seed jumps on restart and unsuccessful events)
I also can use some calculated sequences. for example I can calculate max of the filed values and after incrementing use it as key for new inserted record.
which one is better?
I have been testing my database application for a while now. As a result i have a lot of records already with their respective identity field values. What i want to do is to reset this field to 0 (zero) before i deploy the aplication. I have tried deleting all the records and entered one to see if the id will be 1 but it still starts from where it was before i deleted the records. Any ideas.
View 2 Replies View RelatedI have a sql sever 2005 express table with an automatically incremented primary key field. I use a Detailsview to insert new records and on the Detailsview itemInserted event, i send out automated notification emails.
I then received two automated emails(indicating two records have been inserted) but looking at the database, the records are not there. Whats confusing me is that even the tables primary key field had been incremented by two, an indication that indeed the two records should actually be in table. Recovering these records is not abig deal because i can re-enter them but iam wondering what the possible cause is. How come the id field was even incremented and the records are not there yet iam 100% sure no one deleted them. Its only me who can delete a record.
And then how come i insert new records now and they are all there in the database but now with two id numbers for those missing records skipped. Its not crucial data but for my learning, i feel i deserve understanding why it happened because next time, it might be costly.
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"
Hi all,
Can anyone suggest me on Adding primary key to a table which has already a primary key.
Thanks,
Jeyam
I'm new to SQL and I'm trying to write a statement to satisfy the following:
If [Field1] contains text from [Field2] then return [Field3] as [Field4].
I had two tables where there were no matching keys. I did a cross apply and am now trying to parse out the description to build the key.
Good morning...
I begin with SQL, I would like to add a field that will be date like 21/01/2000.
Actually i find just "datetime" format but give me the format 21/01/2000 01:01:20.
How to do for having date and time in two different field.
Sorry for my english....
Cordially
A newbie
Hi,
I have recently been looking at a database and wondered if anyone can tell me what the advantages are supporting a unique collumn, which can essentially be seen as the primary key, with an identity seed integer primary key.
For example:
id [unique integer auto incremented primary key - not null],
ClientCode [unique index varchar - not null],
name [varchar null],
surname [varchar null]
isn't it just better to use ClientCode as the primary key straight of because when one references the above table, it can be done easier with the ClientCode since you dont have to do a lookup on the ClientCode everytime.
Regards
Mike
We have a table, which has one clustered index and one non clustered index(primary key). I want to drop the existing clustered index and make the primary key as clustered. Is there any easy way to do that. Will Drop_Existing support on this matter?
View 2 Replies View RelatedHi all
I have the following table
CREATE TABLE [dbo].[property_instance] (
[property_instance_id] [int] IDENTITY (1, 1) NOT NULL ,
[application_id] [int] NOT NULL ,
[owner_id] [nvarchar] (100) NOT NULL ,
[property_id] [int] NOT NULL ,
[owner_type_id] [int] NOT NULL ,
[property_value] [ntext] NOT NULL ,
[date_created] [datetime] NOT NULL ,
[date_modified] [datetime] NULL
)
I have created an 'artificial' primary key, property_instance_id. The 'true' primary key is application_id, owner_id, property_id and owner_type_id
In this specific instance
- property_instance_id will never be a foreign key into another table
- queries will generally use application_id, owner_id, property_id and owner_type_id in the WHERE clause when searching for a particular row
- Once inserted, none of the application_id, owner_id, property_id or owner_type_id columns will ever be modified
I generally like to create artificial primary keys whenever the primary key would otherwise consist of more than 2 columns.
What do people think the advantages and disadvantages of each technique are? Do you recommend I go with the existing model, or should I remove the artificial primary key column and just go with a 4 column primary key for this table?
Thanks Matt
I'm using DTS to import data from an Access memo field into a SQL Server ntext field. DTS is only importing the first 255 characters of the memo field and truncating the rest.I'd appreciate any insights into what may be causing this problem, and what I can do about it.Thanks in advance for any help!
View 4 Replies View RelatedI need to pass in null/blank value in the date field or declare the field as string and convert date back to string.
I tried the 2nd option but I am having trouble converting the two digits of the recordset (rs_get_msp_info(2), 1, 2))) into a four digit yr. But it will only the yr in two digits.
The mfg_start_date is delcared as a string variable
mfg_start_date = CStr(CDate(Mid(rs_get_msp_info(2), 3, 2) & "/" & Mid(rs_get_msp_info(2), 5, 2) & "/" & Mid(rs_get_msp_info(2), 1, 2)))
option 1
I will have to declare the mfg_start_date as date but I need to send in a blank value for this variable in the stored procedure. It won't accept a null or blank value.
With refresh_shipping_sched
.ActiveConnection = CurrentProject.Connection
.CommandText = "spRefresh_shipping_sched"
.CommandType = adCmdStoredProc
.Parameters.Append .CreateParameter("ret_val", adInteger, adParamReturnValue)
.Parameters.Append .CreateParameter("@option", adInteger, adParamInput, 4, update_option)
.Parameters.Append .CreateParameter("@mfg_ord_num", adChar, adParamInput, mfg_ord_num_length, "")
.Parameters.Append .CreateParameter("@mfg_start_date", adChar, adParamInput, 10, "")
Set rs_refresh_shipping_sched = .Execute
End
Please help
I embedded a SQL query in excel that gets some datetime fields like "TASK_FINISH_DATE"Â .
How can I convert a datetime field to a date field in SQL in a way that excel will recognize it as a date type and not a text type?
I tried:
CONVERT(varchar(8),TASK_FINISH_DATE ,3)
CONVERT(Date,TASK_FINISH_DATE ,3)
CAST(TASK_FINISH_DATE as date)
**all of the above returned text objectes in excel and not date objects.
I am trying to drag data from Informix to Sql Server. When I kick off the package
using an OLE DB Source and a SQL Server Destination, I get DT_DBDATE to DT_DBTIMESTAMP
errors on two fields from Informix which are date data ....no timestamp part
I tried a couple of things:
Created a view of the Informix table where I cast the date fields as datetime year to fraction(5), which failed.
Altered the view to convert the date fields to char(10) with the hopes that SQL Server would implicitly cast them
as datetime but it failed.
What options do I have that will work?