Currently we're using INT(21)* for all autoincrement id columns in out 30+ table database. We are a blogging site, and have tables storing members, comments, blog posts and the like. I'm quite sure we will never reach the limit of our INT(21) id columns, and would like to know: If using INT(21) when I am sure we'll never need it is ...
Using MySQL 5.1.37 This isn't really a critical issue, one of our biz-dev people just asked about it and I didn't have a good answer. In our users table we have an auto-increment primary key. From time to time, it skips numbers and seems to be doing so at an increasing rate. My original thought was that when two concurrent signups oc...
I need to programatically determine what the primary key field(s) are for a given sqlite table (using sqlite api, not command line). I can get a list of tables and a list of columns, but only see the column type using the Column_Type() function. Need to know if a given column is the primary key (or part of the primary key if a compound...
I came across a question recently that was for "Generating primary key in a clustered environment of 5 App-Servers - [OAS Version 10] without using database". Usually we generate PK by a DB sequence, or storing the values in a database table and then using a SP to generate the new PK value...However current requirement is to generate pr...
When building an application and you are using a table that has a primary key, should you check to see if the table has a primary key or does not have duplicate IDs? I ran into some code I'm maintaining that is checking to ensure no duplicate ids are in the result set. But the id that is being checked is a primary key. So to me this c...
Is there a way in SQL Server to create a table with a primary key that auto-increments itself? I've been looking at the "UniqueIdentifier" type, but this doesn't seem to do what I expect. Currently, I have something like this: CREATE TABLE [dbo].[MyTable]( [Date] [datetime] NOT NULL, [MyField1] [nchar](50) NOT NULL, [M...
I'm not really sure why the following console application doesn't produce the expected behavior for last_insert_id(). I've read that last_insert_id() returns the last auto_incremented value for a particular connection, but in this code, the same result is returned for both connections. Can someone explain where I've gone wrong? st...
Choosing good primary keys, candidate keys and the foreign keys that use them is a vitally important database design task -- as much art as science. The design task has very specific design criteria. What are the criteria? ...
I follow by entity framework example : http://msdn.microsoft.com/en-us/library/bb399182.aspx and I have problem with Identity Columns. Here is part of code of creating database: CREATE TABLE [dbo].[Person]( [PersonID] [int] IDENTITY(1,1) NOT NULL, [LastName] [nvarchar](50) NOT NULL, [FirstName] [nvarchar](50) NOT NULL, ...
In my database application, when I try to attach a page to a site, I get the following error: System.InvalidOperationException: Can't perform Create, Update or Delete operations on 'Table(Junc_Page_Site)' because it has no primary key. I intend a page to be able to be added to multiple sites - by using a junction table. Below is the ...
I am using the Key value of entities in my datastore as the unique identifier in the URL for pulling up a record: http://mysite.appspot.com/myaction/1x7s3fgdlbnRlcklkcicLAbcXc2VyQWNjb3VudCIFYW9uZ This is not a very attractive solution, nor is it SEO friendly, but it's the easiest way I've found to identify an entity uniquely in App En...
Paraphrasing from this MSDN documentation ... An INSERT statement is generated by the Entity Framework and executed on the data source when SaveChanges is called on the ObjectContext. If the INSERT operation succeeds, server-generated values are written back to the ObjectStateEntry. When AcceptChanges is called auto...
How does the PRIMARY KEY keyword relate to clustered indexes in SQL Server? (Some people seem to want to answer this question instead of a different question I asked, so I am giving them a better place to do so.) ...
I am trying to create an Entity that has a byte[12] id in hibernate. It seems like it does not like to have a byte[] as a primary key and as another column it sets it up as a tinyblob in the backing mysql db. I have tried to create a String, but the problem is that the string in java is 2 bytes per char, while it is one byte per char i...
database= mysql language=php I am coding program to work like this PC1 =computer1 step 1.PC1 insert data ,new ID from Auto-increment. 2.PC1 select last ID everything work fine but.. The problem when your code is used by many computers at the same mili-sec. For example PC1insert data,Auto-increment new ID 2.PC2 insert da...
Short story I have a technical problem with a third-party library at my hands that I seem to be unable to easily solve in a way other than creating a surrogate key (despite the fact that I'll never need it). I've read a number of articles on the Net discouraging the use of surrogate keys, and I'm a bit at a loss if it is okay to do what ...
Using the ADO.NET MySQL Connector, what is a good way to fetch lots of records (1000+) by primary key? I have a table with just a few small columns, and a VARCHAR(128) primary key. Currently it has about 100k entries, but this will become more in the future. In the beginning, I thought I would use the SQL IN statement: SELECT * FROM `...
Hello, which is the best primary key to store website address and page URLs? To avoid the use of autoincremental id (which is not really tied to the data), I designed the schema with the use of a SHA1 signature of the URL as primary key. This approach is useful in many ways: for example I don't need to read the last_id from the databa...
I have a set of tables with children of children, like so: Clients (PK ClientID) which is parent (one to many) to Property (PK PropertyID, FK ClientID) which is parent (one to many) to Property Detail (PK PropDetailID, FK PropertyID) and Case (PK CaseID, FK PropertyID). Should the foreign keys for the parent tables be repeated furth...
Hello, I'm working with SQL Server and trying to do a little "reflection," if you will. I've found the system view identity_columns, which contains all of the identity columns for all of my tables. However, I need to be able to select information about primary keys that aren't identity columns. Is there a view that contains data about ...