My User model has the usual id primary key, but it also has a unique login which can be used as an identifier. Therefore, I would like to define routes so that users can be accessed either by id or by login. Ideally, the routes would be something like this: /users/:id (GET) => show (:id) /users/:id (PUT) => update (:id) ... /users/lo...
Hi, Is it possible to have two and more primary/automatic-adding unique IDs per one table in MySQL? For example: Primary Key (INT,11);shop_id;invoice_id 1;200;2001 1;201;2011 2;200;2002 2;201;2012 Is it possible to set this to MySQL table ? -> So there are more primary keys for some other value in the table.. ? I am looking for mor...
Hi - I was told a while ago on this site that the only way to go with a many-to-many (in my case a facebook-ish "friend-ing" system) is to do something like this: uid(PK) friend_id 4 23 4 20 4 54 32 20 32 89 Wont this leave me with lots of identical primary keys (which i believe isn't possible?) And if I ...
We are building an application that may need to be replicated to many servers (I'm hoping not, but there's no way to know if clients will need their own client/server copy until after we release the online product). Since the number of servers is unknown, I've decided to use GUIDs for any transactional table IDs. Since each client ha...
I am using GUIDs as my primary key for all my other tables, but I have a requirement that needs to have an incrementing number. I tried to create a field in the table with the auto increment but MySql complained that it needed to be the primary key. My application uses MySql 5, nhibernate as the ORM. Possible solutions I have though...
Looking for guidance on selecting a database provider for a specific key pattern. The only key field will be a pre-allocated unique sequentially-increasing number. During each day between 50 and 100 thousand items will be added, processed (updated), and then retained for a week or so, after which usually the lowest-numbered records wi...
Hi! I have to two tables namely employees_ce and employees_sn under the database employees. They both have their respective unique primary key columns. I have another table called deductions, whose foreign key column I want to reference to primary keys of employees_ce as well as employees_sn. Is this possible? for example employee...
Although I'm guilty of this crime, it seems to me there can't be any good reason for a table to not have an identity field primary key. Pros: - whether you want to or not, you can now uniquely identify every row in your table which previously you could not do - you can't do sql replication without a primary key on your table Cons: - ...
In the Northwind Starters Kit, Primary keys from database are mapped to Strings in C#. Is this good practice? And if so, why? thx, Lieven Cardoen ps: Sorry for the maybe wrong question... In Northwind Starters Kit some tables have a auto-incremental primary key with datatype int and others have a non auto-incremental primary key wit...
I have table which will only contain information about files, so, as the file path is unique and can identify any entry, i thought i should use it as primary key. Although, i'm having a few problems like needing to specify a key length (error 1170). What should i do? Use an integer as primary key and every time i need to access informat...
My site (ASP.NET/C#/MS-SQL 2005) has multiple sections that allow for comments (User profiles, image pages, videos, etc). I want to store everything in a single comments table. My issue is linking the comment back to its parent. The user profiles' primary keys are uniqueidentifiers, while the images and videos use ints. I'd like to ha...
Is it ok to define tables primary key with 2 foreign key column that in combination must be unique? And thus not add a new id column (Guid or int)? Is there a negative to this? ...
Hi, what if you have so many entries in a table, that 2^32 is not enough for your auto_increment ID within a given period (day, week, month, ...)? What if the largest datatype MySQL provides is not enough? I'm wondering how should I solve a situation where I'm having so many entries added to my table which require unique ID, but I fill...
I've a PK constraint - a clustered index on two columns - which I am in the process of dropping. The command is still running after an hour. I would have thought that as I am just removing a constraint the operation would be nearly instantaneous. Can someone explain to me what is actually happening under the hood when I drop the PK? ...
I have a table which has a clustered index on two columns - the primary key for the table. It is defined as follows: ALTER TABLE Table ADD CONSTRAINT [PK_Table] PRIMARY KEY CLUSTERED ( [ColA] ASC, [ColB] ASC )WITH (SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = OFF) ON [PRIMARY] I want to remove this clustered index PK an...
Hi All, We are trying to come up with a numbering system for the asset system that we are creating, there has been a few heated discussions on this topic in the office so I decided to ask the experts of SO. Considering the database design below what would be the better option. Example 1: Using auto surrogate keys. ================...
I am trying to build a SQL 2005 database that has a Company data table and an Employee data table. Each company should have an Employee 1, Employee 2, and so on. I realize that each Employee record would have a PK of both their EmployeeID and their CompanyID, but I haven't for the life of me determined the best way of doing this? An...
If your ID column on a table is a unique identifier (Guid), is there any point creating a clustered primary key on the ID column? Given that they are globally unique, how would the sorting work? ...
I've been investigating the use of GUIDs as primary keys in databases. So far, the pros seem to outweigh the cons. However, I see one point where GUIDs may not be what I want. In my application, users should be able to identify objects based on a user-friendly ID. So, for example, if they want to get a specific product without typing in...
Hi, I am new to Entity Framework, and ORM's for that mather. In the project that I'm involed in we have a legacy database, with all its keys as strings, case-insensitive. We are converting to MSSQL and want to use EF as ORM, but have run in to a problem. Here is an example that illustrates our problem: TableA has a primary string k...