Set Custom Identity Keys For Master Tables(auto-incrementing):
Jun 17, 2007
I need some help for designing the IDs / Primary keys for some master tables in my database. Following are master tables. Client_Master, Buyer_Master & Seller_Master; I want to set Client_Id, Buyer_Id & Seller_Id as their respective primary keys and they should have following properties
Client_Id :- a) should be auto-incrementing value, b) unique & c)should be of the format – CLXXXXXX, where “CL” {Constant start characters} & “X” {any number 0-9}
Similarly::
Buyer_Id :- BYXXXXXX
Seller_Id :- SLXXXXXX
We are implementing the database in MS-SQL 2005 & MySQL
Can anyone help me find a solution to this, especially in MS-SQL.
View 2 Replies
ADVERTISEMENT
Feb 19, 2008
My Identity value column is not autoincrementing, its seed is set to 1, and increment set to 1.
yet when i click the auto generated 'Add New Record' button in the BindingNavigator control set (in a form where it is populated with databound text boxes), this Identity column is not auto incremented?
And if i try to write a value to this column, i get the error telling me this column cannot be written to. Yet if i do not write to it, then i get "This column does not allow nulls"
To load my data i am using:
Code Snippet
'clear dataset
Me.DsQuote.Clear()
'fill the datagrid with data from tblQuoteID
Me.TblQuoteIDTableAdapter.Fill(Me.DsQuote.tblQuoteID)
and to save data im using:
Code Snippet
Me.Validate()
'end data edit
Me.TblQuoteIDBindingSource.EndEdit()
'update the dataset with table data
Me.TblQuoteIDTableAdapter.Update(DsQuote.tblQuoteID)
anything im doing wrong? this data is being viewed in a datagrid btw.
cheers
View 1 Replies
View Related
Feb 16, 2006
Hi,
I have an auto incrementing int column setup which serves as my unique primary key. Just wondering what happens when the auto increment reaches the limit? Will it recycle numbers from the begining (who's rows have obviously been deleted by this stage)?
View 1 Replies
View Related
Nov 11, 2005
hi there,
i am new to sql server database.i am doing small projects right
now using asp.net and sql to create webpages (very basic webpages)
My problem is:
Problem :
i have two tables .....table 1 and table 2.
Table 1 has following fields: studentid,student name,student address.
Table 2 has following fields:studentid and course .
table1 student id is the primary key refrencing table 2 student id.
Now i delete a record in table 1 which will in turn also get deleted in
table 2 . so for eg if i have three records 1 ,2 and 3 ....then i
delete 2 in table 1 ...i will have 1 and 3 in both table 1 and table
2....now i want 3 to become 2 in both table 1 and table 2...so that i
dont have empty space between two student id's 1 and 3. so this is my
problem....if any one can help me out with suggestions please
do.
thank you all........
ahmed_ind
View 4 Replies
View Related
Mar 11, 2008
My SQL table has the
following columns:
ID – Auto IncrementingNumber_of_Records – IntAccount_Number – Varchar
(Format 1234)Account_Number_Instance – Varchar
(Format e.g. 1234-01)Other_Field1Other_Field2…etc.
When the Account_Number was
initially inserted into the table, a Stored procedure (not written be me) was used to concatenate the Account_Number
and Number_of_Records. As a result
the Account_Number_Instance became,
e.g. 1234-01, etc.
From the ASPX form we
retrieve each of the fields above, and based on the Number_of_Records an
appropriate number of records were inserted for the Account_Number.
For Example:
Account_Number = 12345Number_of_Records = 4
The result inserted into the
table were as follows:
ID # of Records Acct_Number
New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
Now, I would like to UPDATE
this table, and based on the original Number_of_ Records (4) I want to ADD, for
Account_Number (1234) add an appropriate Number_of_Instances, beginning with
the next incremental number and adding what the New Number of Records input is…
For Example:
Account_Number = 12345 New_Num(ber_Of Records) = 3
ID # of Records Acct_Number New_Num Acct_Number_Instance …
1 4 12345 12345-01
2 4 12345 12345-02
3 4 12345 12345-03
4 4 12345 12345-04
5 12345 3 12345-05
6 12345 3 12345-06
7 12345 3 12345-07
I
do realize that the next time I have to update this Account_Number I am also
going to have to somehow ADD only one of the #_of_Records and New_Num for the
Acct_Number instances to obtain the next starting number, which in the example
above would be (8)…
Any
and all suggestions on how to accomplish this would be greatly appreciated!
Thanks
View 2 Replies
View Related
Nov 14, 2007
Hi
I'm using a insert into fuction kind of like below
INSERT INTO table1(
col1
col2
col3
col4
)
SELECT
col2
col3
col4
FROM ...............
I'd like to auto-increment col1 so it counts how many rows are being added
any help is very appreciated
View 5 Replies
View Related
Aug 10, 2006
I have a database table. I need to set a particular data field to allow for Auto Incrementing. Basically I need to set up the initial integer value to start at. Then to specify the amount to increment by.
What I have done so far is to right click on the field name in SQL Server 2005. Then, on the pop-up menu I select the menu option for properties.
When the properties are displayed I see some fields listed in dark grey but I am not able to edit these fields. In other words, if I try to type text in this field, nothing happens. The fields I am talking about are:
Identity Increment
Identity Seed
Is Identity
Does anyone know what the problem is? What do I have to do to edit these fields.
View 2 Replies
View Related
Aug 17, 2006
Here is a simplified version of my problem:
I am inserting data into a table using a stored procedure. The table has an identity column that increments with each insert. When I use erroneous data in the other fields the insert fails….no surprises there! But when the next insert occurs with valid data I find that my identity field has increased even with those inserts that failed, so my sequence has jumped a few numbers. How do I get the identity inserts to roll back if the rest of the data in a row doesn’t insert successfully?
Marcha x
View 9 Replies
View Related
Nov 9, 2006
I am porting a database from Informix to SQL Server 2000. Several fields in the informix database are specified as SERIAL, which means that they auto-increment auto-magically if you insert a 0 into them. Well...I was looking for an equivalent in SQL Server 2000, and thought I had found it in the IDENTITY column concept.
With further investigation into IDENTITY columns, however, I discovered that only one column per table can be given the IDENTITY property. Why is this? Is there a way around that? If not, is there another way to achieve this feature in SQL Server 2000? If not, any suggestions on how I can do it?
Thanks!
Lisa
View 5 Replies
View Related
Sep 22, 2004
Hi everyone,
I am having a table with which i generate a report. Now how to using a SQL query i can generate a auto incrementing no.
Say i am executing this query
SELECT NAME,AGE,ADDRESS FROM MEMBER which gives
NAME AGE ADDRESS
HOLYMAC 13 MALACCA
HOLYCOW 25 USA
HOLYGOD 55 LONDON
Now how can i make it come out like this
SNO NAME AGE ADDRESS
1HOLYMAC 13 MALACCA
2HOLYCOW 25 USA
3HOLYGOD 55 LONDON
See the first column is a auto incrementing number 1,2,3.
How can I write a SQL QUERY that outputs a auto incrementing number.
Thank you
Have nice day
View 2 Replies
View Related
Jul 13, 2001
Hi
Everyone
I have a problem my table structure is like this
book_id varchar2(30)
book_name varchar2(30)
Now i want to enter the data into the fields like this
B001 Java Unleashed
B002 ASp Unleashed
and so on
So my Problemis how i will auto increment the book_id field though that is an alphanumeric field.
PLease help me that how i will insert and select from this table
Waiting for your help
Manish
View 1 Replies
View Related
Aug 10, 2006
Hi All,
I need to set up a kind of identity insert that gives an output in the format: 00001, 00002, 00003 etc. Is there a formatting option for this sort of output using normal identity insert features or do I need to write a function to insert these values (perhaps as text) each time a new record is created?
Sorry if this is really simple but it's only my 4th day in this job!
Marcha
View 5 Replies
View Related
Mar 28, 1999
When replicating a table which has an identity column I get the error: "Procedure cp_insert_tblname expects parameter @C1, which was not supplied.". The stored procedure appears to be called without any parameters so my insert stored procedure does not work. I know I'm missing something basic here!! Do I have to add the field names when telling replication to use a custom stored procedure. If not, how do arguments get passed to my SP, as globals somehow?
Any info greatly appreciated!!
Thanks,
Jay
View 1 Replies
View Related
Jul 13, 2012
We have upgraded our SQL 2008 server to 2012 last month. i've noted since then that many tables that have auto increment primary key bigint field increases by 1 like it should, then for some reason it jumps up by 1000 or even 10000? i have seed adn auto increment set to 1 but doesnt effect it. Is there anything that could be causing the next value to jump that much?
View 18 Replies
View Related
Aug 31, 2007
Hi,
If i were to replicate a database with another where in both the databases have an auto incrementing field. What could be the problems that's goin to arise out of it. How to deal with such a scenario...?????
View 6 Replies
View Related
Apr 1, 2013
Along with the data that I am pulling back from query, I also need to provide an ImportID column with the following requirements:
YYMMDDGP0001, YYMMDDGP0002, YYMMDDGP0003, and so on. The 0001, 0002, and 0003 part could get big into the hundreds of thousands.
I have the YYMMDDGP part down with the following expression:
SELECT CONVERT(VARCHAR(6), GETDATE(), 12) + 'GP' AS [ImportID]
Now I need to get the Auto Incrementing number part of this. I have been researching this trying SELECT INTO a temp table using the Identity Function and declaring different variables all with no luck.
View 7 Replies
View Related
Oct 1, 2014
I need to auto increment an alphanumeric field in a table.
The process is as follows:
1. First position is static letter C for contact or static letter A for account - field contact_id
2. The following 6 positions are numeric - example of the string would be C004658
3. When new contact is entered, it should look up the highest numeric value (in this case 004658) and increment it by one - resulting in C004659
View 9 Replies
View Related
Aug 13, 2007
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
View 7 Replies
View Related
Jan 23, 2004
I have an MS SQL Server table with a Job Number field I need this field to start at a certain number then auto increment from there. Is there a way to do this programatically or within MSDE?
Thanks, Justin.
View 3 Replies
View Related
Jul 16, 2014
what the best practice is for creating indexes on columns that are foreign keys to the primary keys of other tables. For example:
[Schools] [Students]
---------------- -----------------
| SchoolId PK|<-. | StudentId PK|
| SchoolName | '--| SchoolId |
---------------- | StudentName |
-----------------
The foreign key above is as:
ALTER TABLE [Students] WITH CHECK ADD CONSTRAINT [FK_Students_Schools]
FOREIGN KEY([SchoolId]) REFERENCES [Schools] ([SchoolId])
What kind of index would ensure best performance for INSERTs/UPDATEs, so that SQL Server can most efficiently check the FK constraints? Would it be simply:
CREATE INDEX IX_Students_SchlId ON Students (SchoolId)
Or
CREATE INDEX IX_Students_SchlId ON Students (SchoolId, StudentId)
In other words, what's best practice for adding an index which best supports a Foreign Key constraint?
View 4 Replies
View Related
Mar 9, 2007
Is there max number of Database Master Keys that can be used in a single instance of SQL Server 2005?
If I have 12 unique client databases that all need a unique Database Master Key, can this be accomplished?
Thanks ALL!
View 6 Replies
View Related
Nov 17, 2006
What system view do i use to determine is a database has a master key?
Thanks,
Ed
View 1 Replies
View Related
Mar 27, 2007
As part of a project, I'm not allowed to use foreign keys, well can't. But I have a problem with this master detail relationship (and a very simple one too).
Tables to begin with:
Invoice:
invoiceid : bigint indentity autoincrement primarykey
Detail:
invoiceid: bigint - detailnumber:bigint - desc : char20
Okay - I want to set these two tables up such than when I create a new invoice, and subsequently get the new invoiceid, and I start to add detail records to the detail table, I want the detailnumber to autoincrement when I do an SQL Insert. Right now I have detailnumber as the primary key, so when you view the detail records, it looks like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,4, text on another invoice - 3,5, text
I want it to work like:
(invoiceid, detailnumber, desc) 1,1,text - 1,2,text - 1,3,text - 2,1, text on another invoice - 3,1, text
where the invoiceid and the detail number make up the primary key.
I seem to recall you could do this is PHP before it had foreign keys & such, and I'm completely drawing a blank on this. BTW - I'm using enterprise manager to set this up, but I can use QA with a sample script to acheive the same.
I realize this may be (and probably is) off-topic here, but I can't figure out where to ask.
Thanks in advance.
View 3 Replies
View Related
Mar 5, 2014
I am struggling trying to clean some data and identify duplicate records. I used fuzzy grouping in SSIS and provided back a series of groups. The issue is some of the individual records can appear in multiple groups (so in reality the groups should be combined). This is best explained with an example:
Original Data
key1 key2
647942600014
647942285437
2324662490640
2324662285437
2324662066128
2222 2285437
2222 1111111
9999 1111111
9999 2222222
Should look like:
22222600014
22222285437
22222490640
22222066128
22221111111
22222222222
I only choose 2222 as the surviving key because it was the smallest number. I really do not care which number remains as long as it is the same across.
I tried playing with self joins between the tables but have had no success.
I am using Sql Server 2008 and the number of records could 500K to 1MM.
View 2 Replies
View Related
Aug 4, 2015
I have imported a whole bunch of tables. Most of them have an ID (int) column. Is there a way to set the ID columns across all tables to auto increment Primary Keys in bulk?
View 11 Replies
View Related
Aug 2, 2004
I've posted this in the general database forum as well but think it is more sql server specific. I have three tables as below. My problem is that when I insert anything into has_quote, cat_ref and cust_id are both null. Any ideas on how to get round this? Thanks
Code:
CREATE TABLE Item (
cat_ref INT IDENTITY(1,1) PRIMARY KEY,
descrip VARCHAR(50),
date_added SMALLDATETIME,
cat_type VARCHAR(20),
contract VARCHAR(10),
cost_price SMALLMONEY,
supplier_name VARCHAR(20),
supplier_phone VARCHAR(20))
CREATE TABLE has_quote (
quote_id INT IDENTITY (1,1) PRIMARY KEY,
installation_charge SMALLMONEY,
date_of_quote SMALLDATETIME,
commercial_markup SMALLMONEY,
service_desk_contact VARCHAR(20),
category INTEGER,
date_last_pricecheck SMALLDATETIME,
cat_ref INT FOREIGN KEY
REFERENCES Item(cat_ref),
cust_id INT FOREIGN KEY
REFERENCES Customer(cust_id))
CREATE TABLE Customer (
first_name VARCHAR(10),
surname VARCHAR(10),
customer_phone VARCHAR(20),
contract VARCHAR(10),
location VARCHAR(20),
email VARCHAR(50),
cust_id INT IDENTITY (1,1) PRIMARY KEY)
View 3 Replies
View Related
Nov 17, 2003
I have the following issue
- my database consists of tables with one ID field as primary key.
for each INSERT the 'next' value from this ID field is extracted
from a table called TableList.
- this works perfectly fine, as long as I insert one record at a time:
but now I would like to run a command such as
INSERT INTO dest (name)
SELECT name
FROM src
i.e. without being able to specify the ID value.
Has anybody implemented this
(i would prefer not to use identity columns or use cursors),
possible with triggers?
thanks for your time,
Andre
View 1 Replies
View Related
Jan 26, 2006
This ain't working
SELECT T.TABLE_NAME,C.COLUMN_NAME
FROM INFORMATION_SCHEMA.TABLES T
JOIN INFORMATION_SCHEMA.COLUMNS C
ON T.TABLE_NAME = C.TABLE_NAME
WHERE OBJECTPROPERTY(OBJECT_ID(T.TABLE_NAME),
'TableHasIdentity') = 0
AND T.TABLE_TYPE = 'BASE TABLE'
AND OBJECTPROPERTY(OBJECT_ID(C.COLUMN_NAME),'IsPrimary Key') = 1
ORDER BY T.TABLE_NAME,C.COLUMN_NAME
This is giving me bogus results...
SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE OBJECTPROPERTY(OBJECT_ID(COLUMN_NAME),'IsPrimaryKe y') = 1
I have PK's all over the place. What gives? Too many cocktails with lunch?
View 1 Replies
View Related
Aug 31, 2014
can i create Custom Auto-Generated Sequences with SQL Server like DD-0001,DD-0002,DD-0003...... to DD-000........
View 1 Replies
View Related
May 29, 2012
We are facing the following issue, several machines/users that are executing very often a command similar to :
INSERT INTO TableName (FieldOne,FieldTwo) VALUES ('ValueOne','ValueTwo');
SELECT SCOPE_IDENTITY() AS Table_ID;
Where TableName has a primary key defined as identity(1,1).and that Table_ID is being used as reference in others tables
These queries are executed using different dababase users and among several diffrent apps..The Problem is that we are detecting lost block of "Table_ID's" as the other tables shows the InsertedID as a reference, but the TableName table lacks of this ID record. In other words, the INSERT seems to work, the SCOPE_Identity returns an InsertedID, and the other tables are populated using this number. However, when we query the TableName table the mentioned record does not exist. We are profiling the server and we're sure that there are no DELETE statement on the TableName table. This seems to be happening when the are either deadlocks or blocked processes. Whenever the deadlocks and locks disappear/solved, everything works as expected.why the Scope_Identity returns the Inserted ID if the INSERT action had failed.
View 4 Replies
View Related
Nov 2, 2006
Hi all!
I'm just getting my feet wet with how encryption works in SQL 2005. With regards to the encryption of primary / foreign keys, I'm not entirely clear on the best approach. Below are three examples of typical table structures I currently have:
== Customers table ==
CustomerID (PK, int, Identity)
CustomerName (varchar)
== Orders table ==
OrderID (PK, int, Identity)
CustomerID (int, foreign key)
CreditCardNumber (varchar)
== OrderDetails table (1 to Many) ==
OrderID (PK/FK, int)
ItemNumber (PK, int)
ItemDescription (varchar)
The Customers and Orders tables use identity values as their primary keys. From what I can tell, CustomerID in the Customers table cannot be encrypted and OrderID in the Orders table cannot be encrypted because they are identity values. In these cases, would it be safer (in terms of security) to create a separate, meaningless identity key column in the Customers table and then remove the identity attribute from CustomerID so I can encrypt CustomerID?
Similarily in the OrderDetails table, OrderID and ItemNumber form a composite key. These values are important in that I don't want them to be tampered with. Am I better off creating a separate identity key column which becomes the table's primary key ... then encrypt both the OrderID and ItemNumber columns in this table?
Any ideas are appreciated.
Thank you,
Ben
View 1 Replies
View Related
Jan 9, 2007
Hello,
I am working on an accounting system using VB.NET and sql server 2005 as a database. the application should be used by multiple users.
i have a the following structure:
Voucher: ID (primary), Date,TypeID, ReferenceCode, .....
Type: ID, Code, Name. (the user can add new type anytime!)
(Ex: PV- payment voucher, JV - Journal Voucher ,....)
When adding a voucher the user will choose a type, according to this type (for each year) a counter will be increminted.
for example: PV1, PV2....PV233,... the other type will have its separate counter JV1, JV2 ,...JV4569,..
I am using the sqlTransaction cause i am doing other operations that should be transactional with the insertion of the Voucher.
The question is :
What is the best solution to generate a counter for each type?(With code sample)
Thanks.
View 4 Replies
View Related
Oct 8, 2007
Hi,
I have the following two tables:
Code:
create table RECORD
(
ID int not null,
Issue_descr varchar(256) not null,
Priority varchar(5) not null,
Status varchar(12) not null,
Date_add varchar(10) not null,
Date_due varchar(10) not null,
Date_complete varchar(10),
PName varchar(32),
primary key(ID),
foreign key(PName) references PROJECT(PName)
);
and
Code:
create table STEPS
(
ID int not null,
Num int not null,
Descr varchar(256),
Date_due varchar(10) not null,
Date_complete varchar(10),
Status varchar(12),
primary key(ID, Num),
foreign key(ID) references RECORD(ID)
);
I have set PK "ID" in table RECORD to auto identity(1,1). I have done the same for PK "num" in table STEPS.
However I am seeking this behavior in STEPS:
ID num
-- ----
19 1
19 2
19 3
20 1
20 2
21 1
but what I'm getting is PK num doesn't "reseed" or reset to 1 as "ID" changes. PK num just auto-increments regardless of ID. Is there a workaround?
Thanks.
View 2 Replies
View Related
