Here we go again, the old argument still arises... Would we better have a business key as a primary key, or would we rather have a surrogate id (i.e. an SQL Server identity) with a unique constraint on the business key field? Please, provide examples or proof to support your theory. ...
In mysql, how do I get the primary key used for an insert operation, when it is autoincrementing. Basically, i want the new autoincremented value to be returned when the statement completes. Thanks! ...
I'm designing a small SQL database to be used by a web application. Let's say a particular table has a Name field for which no two rows will be allowed to have the same value. However, users will be able to change the Name field at any time. The primary key from this table will be used as a foreign key in other tables. So if the Name...
A team member has run into an issue with an old in-house system where a user double-clicking on a link on a web page can cause two requests to be sent from the browser resulting in two database inserts of the same record in a race condition; the last one to run fails with a primary key violation. Several solutions and hacks have been pr...
Has someone ever measured performance of Sequential Guid vs. Standard Guid when used as Primary Keys inside a database? ...
Currently, primary keys in our system are 10 digits longs, just over the limit for Java Integers. I want to avoid any maintenance problems down the road caused by numeric overflow in these keys, but at the same time I do not want to sacrifice much system performance to store infinitely large numbers that I will never need. How do you ha...
I have a number of child tables that have a foreign key to a parent table. How do I add an entry in the parent table and get the primary key of that entry, so that I can then enter rows in the child tables that point to the entry in the parent table? I'm doing this in a MS Access Database from a C# application. ...
I need a function which executes an INSERT statement on a database and returns the Auto_Increment primary key. I have the following C# code but, while the INSERT statement works fine (I can see the record in the database, the PK is generated correctly and rows == 1), the id value is always 0. Any ideas on what might be going wrong? ...
If I try to insert data with a 20 digit primary key into a SQLite database I get an error with the second insert statement because it "is not unique". If I select the values I can see from the SQLite commandprompt that the primary key is written in scientific notation. The column type is decimal. Is there a way to force SQLite to insert...
Hello! I was wondering, is there any possibility to create a table without a primary key, but with two foreign keys, where the foreign keys pairs are always different? For example, a STOCK table with item_id and warehouse_id as foreign keys from ITEMS and WAREHOUSES tables. So same item can be in different warehouses. The view of the tab...
I'm new to database development so hopefully this is trivial. I have a large collection of raw data (around 300million rows) with about 10% replicated data. I need to get the data into a database. For the sake of performance I'm trying to use SQL copy. The problem being when I commit the data, primary key exceptions prevent any of the d...
Can i have multiple primary keys in a single table? ...
I've read that compound primary keys will confuse the hell out of typical ORM code generators. Which ORMs work best with compound PKs and which to avoid? (I've a particular interest in .NET) ...
I'm building a database that will store information on a range of objects (such as scientific papers, specimens, DNA sequences, etc.) that all have a presence online and can be identified by a URL, or an identifier such as a DOI. Using these GUIDs as the primary key for the object seems a reasonable idea, and I've followed delicious and ...
I am currently using... select Table_Name, Column_name, data_type, is_Nullable from information_Schema.Columns ...to determine information about columns in a given database for the purposes of generating a DataAccess Layer. From where can I retrieve information about if these columns are participants in the primary key of their table...
I'm considering altering some tables to use nvarchar(50) as primary key instead of an int primary key. Using an int ID for a key really is irrelevant data, it's the string I'm interested in. What sort of performance hit will occur, or where do you research this? Other than cut and try that is. ...
How would I reset the primary key counter on a sql table and update each row with a new primary key? ...
I ran into the problem that my primary key sequence is not in sync with my table rows. That is, when I insert a new row I get a duplicate key error because the sequence implied in the serial datatype returns a number that already exists. It seems to be caused by import/restores not maintaining the sequence properly. ...
I acquired a database from another developer. He didn't use auto_incrementers on any tables. They all have primary key ID's, but he did all the incrementing manually, in code. Can I turn those into Auto_incrementers now? ...
For example if I have an auto-numbered field, I add new records without specifying this field and let DB engine to pick it for me. So, will it pick the number of the deleted record? If yes, when? // SQL Server, MySQL. // Follow-up question: What happens when DB engine runs out of numbers to use for primary keys? ...