Since DBs do not reuse numbers of deleted records it is possible to run out of numbers, especially if you pick not really a big integer type for this column. What would happen and how to prevent it if it's bad? // SQL Server, MySQL // ...
I'm currently working on someone else's database where the primary keys are generated via a lookup table which contains a list of table names and the last primary key used. A stored procedure increments this value and checks it is unique before returning it to the calling 'insert' SP. What are the benefits for using a method like this (...
I have a table in a database that represents dates textually (i.e. "2008-11-09") and I would like to replace them with the UNIX timestamp. However, I don't think that MySQL is capable of doing the conversion on its own, so I'd like to write a little script to do the conversion. The way I can think to do it involves getting all the record...
Hi, On my current project, I came across our master DB script. Taking a closer look at it, I noticed that all of our original primary keys have a data type of numeric(38,0) We are currently running SQL Server 2005 as our primary DB platform. For a little context, we support both Oracle and SQL Server as our back-end. In Oracle, our pri...
I'm now to this point of my project that I need to design my database (Oracle). Usually for the status and countries tables I don’t use a numeric primary key, for example STATUS (max 6) AC --> Active DE --> Deleted COUNTRIES (total 30) UK --> United Kingdom IT --> Italy GR --> Greece These tables are static, not updated through the a...
Is there a measurable performance difference between using INT vs. VARCHAR as a primary key in MySQL? I'd like to use VARCHAR as the primary key for reference lists (think US States, Country Codes) and a coworker won't budge on the INT AUTO_INCREMENT as a primary key for all tables. My argument, as detailed here, is that the performanc...
I'm using TopLink as my ORM and MySQL as the DB. I traded my auto-increment primary keys for GUIDs for one of my tables (alright, not quite: I'm actually using a random 64 bit integer, but that's good enough for my needs). Anyway, now queries, which don't even use the key, are taking much longer. What can I do? ...
I have a small dataset with a few datatables. I load the datatables from various DBs and have a config file that I read to determine the primary keys I want to enforce on the given datatables. If the config doesn't contain a proper (non unique) primary key, how can I catch this event when applying the primary key to the datatable? Cur...
Consider this example table (assuming SQL Server 2005): create table product_bill_of_materials ( parent_product_id int not null, child_product_id int not null, quantity int not null ) I'm considering a composite primary key containing the two product_id columns (I'll definitely want a unique constraint) as opposed to a sep...
In a fairly animated discussion in my team I was made to think what most people like as primary keys. We had the following groups- Int/ BigInt which autoincrement are good enough primary keys. There should be at least 3 columns that make up the primary key. Id, GUID and human readable row identifiers all should be treated differently. ...
I'm trying to create a bunch of entries in a database with a single script and the problem I'm encountering is how to reference the generated primary key of the previous entry I created. For example if I created a customer, then tried to create an order for that customer, how do I get the primary key generated for the customer? I'm usi...
Assume a table that uses a natural key and has a number of existing rows. What would be the easiest way to retrofit a surrogate key column and populate it with unique values using MySql? I.e. transform table employees ( social_security_no varchar(20), ... constraint emp_pk primary key (social_security_no) ); to table emp...
This code works, but i dont understand why. With DeferredLoadingEnabld = false, I would expect it not to return the primary key. Can someone explain what I am missing? public void SaveOrder (Order order) { using (DataContext dc= new DataContext) { dc.DeferredLoadingEnabled = false; ... or...
Say I have an ID row (int) in a database set as the primary key. If I query off the ID often do I also need to index it? Or does it being a primary key mean it's already indexed? Reason I ask is because in MS SQL Server I can create an index on this ID, which as I stated is my primary key. Edit: an additional question - will it do any ...
Hello all, I've currently got a database with about 20 reference tables, i.e. stuff like products, assets, depots, users, etc. This information is stored in a central database and is downloaded to PDAs of engineers who are out on the road. Every table in my database has a PK of UniqueIdentifier (i.e. a GUID). I've realised after 2 year...
I have many tables in my database which are interrelated. I have a table (table one) which has had data inserted and the id auto increments. Once that row has an ID i want to insert this into a table (table three) with another set of ID's which comes from a form(this data will also be going into a table, so it could from from that table)...
I have an HSQLDB database with a generated ID and I want the auto-incrementing values to always be above 100,000. Is this possible with HSQLDB? Is this possible with any database? ...
Anyone know of some good tutorials on how to create a custom ID generator for hibernate? ...
We had a meeting this morning about how would should store our ID for some assets that we have in our database that we are making, the descusion generated a bit of heat so I decided to consult the experts of SO. The table structure that I belive that we should have(short version) is like the following: Example 1) AssetId - int(32) - ...
I am not very familiar with databases and the theories behind how they work. Is it any slower from a performance standpoint (inserting/updating/querying) to use Strings for Primary Keys than integers? ...