DB Design :: Define Automatic Sequence On A Table Primary Key Field?
Jun 13, 2015
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?
View 3 Replies
ADVERTISEMENT
Jan 19, 2005
I need create a field to store tax rate. I need only 2 decimal points. I defined the field as decimal, precision=5 and scale=2. Does it mean that it can hold value from 0.00 to 999.99?
View 12 Replies
View Related
Sep 25, 2005
i have a student table and i created a stored procedure to insert a new student in this table but student_id field wich i put it as primary key got error because allready record has same value .how i can know the last row's student_id value and input a new valid value in one stored procedure thanks
View 1 Replies
View Related
Sep 3, 2015
We want to add a new int identity column as a primary key to an already existing table that has a primary key on Guid. Here is the DDL:
CREATE TABLE [dbo].[VRes](
[VResID] [uniqueidentifier] NOT NULL,
[Mes] [varchar](max) NOT NULL,
[PID] [uniqueidentifier] NOT NULL,
[Segt] [int] NOT NULL,
[code]....
Also we currently have 3 million rows on this table. Is having an integer column as identity column and primary key better or shd I consider using BigInt?
View 4 Replies
View Related
Nov 6, 2015
I have come up with one scenarios where I have three table like Product, Services and Subscription. I have to create one table say Bundle where I can have some of the product id , service id and Subscription id , i.e. a bundle may contains sum prduct , services and subscription . How I can design these relations ?
View 3 Replies
View Related
Sep 24, 2014
I've two tables A, B. In A table, I need to define the primary key with combination of 2 columns and this Primary Key will be a foreign key in table B. Based on these PK and FK I'll be writing a join to get the second column in table B.
View 0 Replies
View Related
Dec 5, 2007
i am putting together an application for a client of mine that involves image heavy data handling. i have a couple of possible methods in mind of how to go about this, and would like some outside opinions, especially from any of you who may have done anything similar before.
to quickly sum up the application's desired results: a photo gallery that contains four sections of job before/after photos. 1)residential 2)comercial 3) automotive and 4)specialty. when a site browser clicks a section, it will bring up a gridview of the "key" or ideal photo (one each)from several different jobs. the site browser can then click the photo, and a new page (or new control, does'nt make much difference) will come up showing all photos for that particular job.) past and current customers will create user logins to have ability to upload photos of their specific job. admin needs ability to 1)upload photos for many jobs 2)review all photos before posting them to the gallery 3) would like to give admin ability to order the photos within the gallery and within each job 4)admin needs ability to create tagline with short description either for each photo or at least for each job.
as to the design of the data, i can either store the images themselves in the database, or create tables to tag the images with various identifying data, then simple store and call the images in a directory on the server. i have code to do either. i am trying to construct a database design to handle all issues. i like the idea of storing photos in the database more, just not sure how to architect it. it would be the 4 albums, each with infinitely create-able 'job' albums within, each job album housing job photos.
i am thinking the images maybe get a table for their specific info (ID as primary key, image itself, file type, length, and a foriegn key to a job table that has jobID primary key) the job table could have a job name that user inputs, thus identifying all photos to the same job. the job table would also foriegn key to the album table id, with its four categories of residential, automotive, commercial, and specialty. does this method seem feasible? if this system seems workable, i can throw in a boolean default to false for the admin to set true to display the job photos as a whole, with admin power to delete photos on an individual basis. but i am a little stumped how he can maybe order them? maybe an int field that he can edit and just manually put the jobs in the order he wants? and then put the photos under the jobs in the order he wants as well?
it seems i have somewhat thought this out just now while writing, so it seems i have a somewhat workable solution, if awkward, but if anybody knows a simpler, or tried and true, method, please let me know
any ideas, suggestions, and help is greatly appreciated.
View 23 Replies
View Related
Feb 11, 2008
CREATE TABLE [dbo].[tblProve](
[ID][varchar](50)NOT NULL,
[tempCountrys][varchar](100) NULL,
CONSTRAINT [PK_tblProve] PRIMARY KEY CLUSTERED
([ID] ASC) WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
I have a table "dbo.tblProve" that have a Primery Key ID of type
varchar(10) and this primary key have a Default value or Binding like this:
([dbo].[fnc_ProveNewID]())
This function "dbo.fnc_ProveNewID()" finds the max(ID) in the same table and creates the next ID.
When I insert a record into the table dbo.tblProve the function creates the new ID automatically (this is the format of the ID '0000000001' or '000000010' etc. ). But when I insert a lot of records from a Select Query it displays this message below:
Msg 2627, Level 14, State 1, Line 2
Violation of PRIMARY KEY constraint 'PK_tblProve'. Cannot insert duplicate key in object 'dbo.tblProve'.
The statement has been terminated.
Is there any way to create a new ID into a table automatically without to be inserted by the user or client?.
View 8 Replies
View Related
Nov 19, 2015
I have a table named [Customers] which has about 100 fields and tens of thousands of records. These fields have been created over a couple years, but now they are so disorganized its getting crazy. I want to move the fields using click and drag on the record selectors the GUI in design view of SQL Server Management Studio, but I am worried about possible dangers. Is there anything I should be worried about by doing this before saving the changes? I don't want to make a view because the front end program is pretty big and would require way too much reprogramming to point all its parts to a new view on a table used so much already.
The question arises because when I try, it wants to also save all tables that are FKs to this table, which are many(and have cascade updates on). So I decided to abort and get info I should know first. Although the data shouldn't change when moving the field, I don't want to just assume that nothing will go wrong.
Also, the system is live, and it isn't easy to shut down to do this change. Is it essential that the system be offline, or at least has no users connected to the tables?
View 3 Replies
View Related
May 17, 2005
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
View 11 Replies
View Related
Feb 23, 2001
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
View 1 Replies
View Related
Apr 10, 2015
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 ?
View 4 Replies
View Related
Jul 31, 2007
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!
View 2 Replies
View Related
Oct 17, 2005
I am currently undertaking a review of the primary keys in a SQL Server2000 database with a view to improving performance of queries.I have heard that, in the case of compound primary keys, it isimportant to select the correct order for the columns within the key.For instance, imagine a table called OrderLine which has primary keycolumns as follows-LedgerOrderNumberOrderLineNumberThe theory I have heard is that columns with the most distinct valuesshould come first. In this case, Ledger is likely to have a maximum of6 distinct values, OrderNumber a maximum of 10 million and OrderLine upto 99. Based on this supposition I believe the best order would be-OrderNumberOrderLineNumberLedgerI have performed a few rudimentary tests that appear to bear this out.I was wondering if anyone else has tried something similar and if sowhat was the result?Thanks,Ross
View 14 Replies
View Related
Aug 10, 2007
what other method can you use to generate primary keys automatically. My boss disagrees with me using identity for the primary keys. He says it is not professional. I am working on SQL Server 2005 platform. Can anybody advice me?
View 3 Replies
View Related
Oct 24, 2007
Hi everyone,
Primary platform is sql25k running with sp2.
I'd like to define for a concrete field which only could have three values:
"a","b","c"
TIA
View 7 Replies
View Related
Jun 18, 2007
I 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.
View 5 Replies
View Related
Mar 31, 2008
I have a database in XML format , with 100+ tables, that I want to import into SQL server but the problem is that I don't have any of the original primary key information. I know that some of the tables had multiple primary keys.
Is there a method or a tool that is capable of determining the primary keys for me??
I don't want to have to figure it out by hand, especially if I have more of these databases in the future.
View 4 Replies
View Related
Dec 28, 2007
Hi all,
I am trying to add a filter to the data of a chart as following:
Expression: =Fields!Time.Value
Operator: >
I tried to the following for the value:
12/28/2007
2007-12-28
'2007-12-18'
'12/28/2007'
...
I always get an error saying:
"... Cannot compare data for types System.DateTime and System.String. Please check the data type.."
Any tip will be greatly appreciated.
View 13 Replies
View Related
Aug 28, 2015
I desire to have a clustered index on a column other than the Primary Key. I have a few junction tables that I may want to alter, create table, or ...
I have practiced with an example table that is not really a junction table. It is just a table I decided to use for practice. When I execute the script, it seems to do everything I expect. For instance, there are not any constraints but there are indexes. The PK is the correct column.
CREATE TABLE [dbo].[tblNotificationMgr](
[NotificationMgrKey] [int] IDENTITY(1,1) NOT NULL,
[ContactKey] [int] NOT NULL,
[EventTypeEnum] [tinyint] NOT NULL,
[code]....
View 20 Replies
View Related
Feb 7, 2007
Hi all,
In my project i will have the data in a collection of objects, I need to update series of tables with foreign key relations
Right now my code looks like this
foreach(object obj in Objects){
int accountId=Account.Insert(obj.accountOpenDate,obj.accountName);//this will update the accounts table and returns account id which is a Identity column in Acccounts table
int DebtId=Debt.Insert(accountd,obj.debtamount,obj.debtbalance); this will update the Debts table and returns DebtId
///series of tables like above but all the relevant data comes from obj and in the Insert Methods i am using stored procedures to Insert the data into table
}
The no of objects varies from 1000 to 1 milliion,, with this approach its taking more time to update all the data. Please let me know if any alternative procedure to handle this kind of scenario.
Thanks
Regards
Prasad.
View 2 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
Nov 17, 2007
Hi,I'm creating a database using SQL Server 2005 Express Edition (Comes with Visual Web Developer). The table which I am creating has the following Fields - all don't allow nulls:IDUserIdDateDescription(UserId is a foreign key to asp_net_Users as I am supporting user accounts)Basically what I need to do is create a page where I as an Administrator can log onto and enter just the text for the field Description. Then once I upload this I wish all users to visit the site and view this Description on a page however with it also listing the Administrator who wrote it along with the Date. I wish both of these fields to be added automatically (UserId to display the User Name and the Date to display the date and time with which the Description was added - However these need to be editable by the Administrator if he/she wishes to change them).Can anyone point me in the right direction on the steps needed to create this scenario?Thanks for any helpDaniel
View 4 Replies
View Related
Jan 1, 2008
Hi, I have a question regarding inserting numbers automatically. For example, there are 2 fields. One is CountryID and other is CountryName. From Front-end I'm entering the Country Name "India" alone and in my table the CountryID "1" or what ever the sequence should be entered. I believe this can be done using Triggers. I read on KB article in Microsoft website. But didn't get clear idea. Is that the correct way?Can any one show me a small example of this? Or give an idea for this?
View 5 Replies
View Related
Jun 15, 2004
I need to automatically update a datetime field for a record to the current time whenever the record is updated.
create table t (
id bigint identity(1,1) not null primary key,
name varchar(50),
value varchar(50),
ts datetime not null default getutcdate()
)
go
insert t (name, value) values ('fred', 'bob')
go
update t set value='robert' where id=1 and name='fred'
go
One option would be to use an instead of update trigger.
create trigger update_t on t
instead of update as
update t set ts=getutcdate(),name=inserted.name, value=inserted.value from t inner join inserted on t.id=inserted.id
go
update t set value='dick' where id=1 and name='fred'
go
Sounds like I've solved my own problem, heh? Well, here's the catch ... you can't know the names of the other columns at the time you write the trigger. I.e. you only know that there is a ts field that needs to be updated internally, otherwise you want the update to do the same thing it would normally do.
Any ideas?
View 1 Replies
View Related
Mar 25, 2002
What I want to do is: insert into newtable (field1, field2, sequence) select
(fielda, fieldb, #) from oldtable and have the sequence field "re-seed" to 1 every time the value in fielda/field1 changes. This added requirement makes this not an identity field problem, if I understand identity fields. This is a data conversion problem, so I'm converting 1000's of rows from oldtable to newtable and want the sequence to re-seed at every change in value of the fielda/field1 data; in fact fielda/field1 is a simplification as there could be multiple controlling fields forcing a re-seed.
TIA...Al
View 2 Replies
View Related
Jun 5, 2012
I have created a view based on joining 3 tables, however, it is not possible to have a unique field in the view which I must need it and I must create index on some other fields. Is there any way to create sequence number or uniqie field in mssql view.
View 13 Replies
View Related
Jul 25, 2015
I started to working on a project. I dont have a lot experiences except basic information. I just want to ask can I use symbols in primary key column like 2015/1234 etc. for file numbers. These are file numbers and must be unique so i decided to make them primary keys but im not sure that i can use symbols.
View 2 Replies
View Related
Oct 8, 2015
The following works just fine. The table tmpMHPCLMDET does have a column ADMTDT ( varchar(8) ).
While I am adding the sequence of numbers I like it to be sorted based on ADMTDT column.
What that means is the row with the earliest ( smallest ) ADMTDT will get 1 and the next 2 and so on.
Declare @ID int
If Exists ( Select c.name from sys.columns c where object_id = object_id('tmpMHPCLMDET') and C.name = 'ServiceLineID' )
Begin
--Adding a sequence of numbers to the ServiceLineID column.
SET @id = 0
UPDATE tmpMHPCLMDET
SET @id = ServiceLineID = @id + 1;
End;
View 2 Replies
View Related
Apr 11, 2004
Why is it not possible to define more than one relationship per table?
i have a primary table that i would like to cascade deletes to 2 other foreign tables in 2 separate relationships. why can't i do this and what are my alternatives?
thank you
View 4 Replies
View Related
Apr 5, 2005
Hi!
I have seen two approaches to primary keys. First one - and it is like
default - is to use surrogate key as primary key. For each table I will
create some autonumeric field hat cannot be changed once it has value.
Some materials refer to this key also as technical primary key. I
design my databases this way usually.
The other approach is to create primary key of fields that make primary
key on database logical model. This approach is not so popular and has
some side effects like a little bit clumpsy looking joins and
unconvenient use in applications.
Question: What is the main idea behind second approach? Or how explain their preference database designers who are using second approach?
View 6 Replies
View Related
Apr 30, 2014
choosing a primary key for the database which i am designing.
I have few tables which contains 5 -15 fields out of it 3 - 9 columns combined to form the uniqueness of the row.
All are un-related tables. Three parent tables connect with 20 child non-related child tables.
I believe it would not be a wise choice to choose 3 to 9 fields for primary key. But if i use an auto increment as a key will there be of any use as it might never be used to fetch rows. Then why do i still have to go with that?
Or Is it ok to create a primary key of upto 5 attributes?
View 9 Replies
View Related
Jul 14, 2015
I have a database around 500 GB. right now the database have only one data file and one log, it has only one filegroup also.all the indexes and table are placed in Primary Filegroup . we are going to separate them. the planing is to move all the indexes to Secondary filegroup and all the table will be in Primary filegroup.But there will be a problem while implementing it because there are around 600 tables and each table have at least 2 non-clustered index , so is there any way to move all the index to Secondary Filegroup.
View 3 Replies
View Related
