EDIT: I have added "Slug" column to address performance issues on specific record selection. I have following columns in my table. Id Int - Primary key (identity, clustered by default) Slug varchar(100) ... EntryDate DateTime Most of the time, I'm ordering the select statement by EntryDate like below. Select T.Id, T.Slug, ..., T.Ent...
My code works. After I copy about 10 tables I get an error. Dynamic SQL generation for the DeleteCommand is not supported against a SelectCommand that does not return any key column information. Okay, I know I need to generate a primary key. But why can I copy 10 or so tables over, and THEN I get the error. Does each row have to return a...
How do I retrieve a multi-column PK in MySQL? For example I have my primary key setup as PRIMARY KEY (donor_id,country_id) Now if I want to get the primary key value without concatenating those 2 fields in a select query, how do I do that? I want to use this in a view (or better yet, directly in phpmaker). ...
I have been wondering what the best practices or ramifications are in setting up the PK in a M2M table in SQL Server. For instance: I have 2 tables Users Roles I am making a new table UserRole Which has 2 fields RoleId & UserID now should I create a UserRoleID as the PK and make UserID and RoleID the FKs make the PK UserID ...
If I set a primary key in multiple columns in Oracle, do I also need to create the indexes if I need them? I believe that when you set a primary key on one column, you have it indexed by it; is it the same with multiple column PKs? Thanks ...
As a rule is it better to use native primary keys (ie existing columns or combination of columns) or set your primary key to an auto generating row of integers? EDIT: It has been pointed out to me that this very similar to this question. The consensus here is to use surrogate keys, which was my natural inclination, but my boss told me ...
We have a system that uses UniqueIdentifier as the primary key of each of the tables. It has been brought to our attention that this is a bad idea. I have seen similar post on the subject but I am interested in any MS SQL performance and other potential problems I may encounter due to this decision. ...
I added a new ADO.Net Entity Data Model into my project and used the Update Wizard to add tables into the model. Five of the selected tables were added to the design surface. Two other tables will not add. I select them in the wizard and click Finish, yet they never show up on the design surface. Is this a bug, or are there some situ...
I have been working on an application in Django. To begin with, for simplicity, I had been using sqlite3 for the database. However, once I moved to PostgreSQL, I've run into a bit of a problem: the primary key does not reset once I clear out a table. This app is a game that is played over a long time period (weeks). As such, every t...
I'm implementing a tag system similar to StackOverflow tag system. I was thinking about when storing the tags and relating to a question, that relationship will be directly with the tag name or it's better create a field tagID to "link" the question with the tag? Looks that linking directly to tag name is easier, but it doesn't look good...
Hi, I am having a very small tables with at most 5 records that holds some labels. I am using Postgres. The structure is as follows: id - smallint label - varchar(100) The table will be used mainly to reference the rows from other tables. The question is if it's really necessary to have a primary key on id or to have just an index on ...
I've just found the following code: select max(id) from TABLE_NAME ... ... do some stuff ... insert into TABLE_NAME (id, ... ) VALUES (max(id) + 1, ...) I can create a sequence for the PK, but there's a bunch of existing code (classic asp, existing asp.net apps that aren't part of this project) that's not going to use it. Should I ...
I have a table named Permissions which is defined in a dataset named _permissionsSet and it has 3 columns: PermissionGroup, Permission, PermissionLevel (in that order) I set the primary key on the table to include all 3 columns like this DataColumn[] keys = new DataColumn[3]; keys[0] = _permissionsSet.Permissions.Colum...
Is it possible to have phpmyadmin or other tool to import a csv or sql backup into an existing DB and skip the primary key or is this a manual, table by table process of inserting with queries and manually removing the primary key? ...
I am wondering how to 'seed' an auto incrementing primary key value using Castle AR? For Example wanting the Orders table primary keys to start out as 10000. Is this something that is 1. possible 2. a good solution for creating order numbers? Maybe there is a way to have consecutive auto incrementing field on the DB that is NOT the pk...
Hi, Please clear my doubt about this, In SQL Server (2000 and above) is primary key automatically cluster indexed or do we have choice to have non-clustered index on primary key? ...
Hi, I've a table with large number of rows (10K+) and it primary key is GUID. The primary key is clustered. The query performance is quite low on this table. Please provide suggestions to make it efficient. ...
We have a legacy database that uses strings as primary keys. I want to implement objects on top of that legacy database to better implement some business logic and provide more functionality to the user. I have read in places that using strings for primary keys on tables is bad. I'm wondering why this is? Is it because of the case-se...
Basically, I will need to combine product data from multiple vendors into a single database (it's more complex than that, of course) which has several tables that will need to be joined together for most OLTP operations. I was going to stick with the default and use an auto-incrementing integer as the primary key, but while one vendor s...
I'm not very familiar with cake.. So here's my questions.. we're developing an app on mysql, but it may eventually need to deploy to mssql or oracle. How do we make sure that we won't have strange problems with our primary keys? In mysql they are AUTO INCREMENT columns but IIRC in oracle you need to use sequences... is there a way to mak...