Primary Key: There can only be one primary key constraint in a table In some DBMS it cannot be NULL – e.g. MySQL adds NOT NULL Primary Key is a unique key identifier of the record Unique Key: Can be more than one unique key in one table Unique key can have NULL values It … Read more
Technically yes, but if a string makes sense to be the primary key then you should probably use it. This all depends on the size of the table you’re making it for and the length of the string that is going to be the primary key (longer strings == harder to compare). I wouldn’t necessarily … Read more
GUID vs.Sequential GUID A typical pattern it’s to use Guid as PK for tables, but, as referred in other discussions (see Advantages and disadvantages of GUID / UUID database keys) there are some performance issues. This is a typical Guid sequence f3818d69-2552-40b7-a403-01a6db4552f7 7ce31615-fafb-42c4-b317-40d21a6a3c60 94732fc7-768e-4cf2-9107-f0953f6795a5 Problems of this kind of data are:< – Wide distributions of … Read more
We had the same problem and this is the solution: To force entity framework to use a column as a primary key, use ISNULL. To force entity framework not to use a column as a primary key, use NULLIF. An easy way to apply this is to wrap the select statement of your view in … Read more
Unfortunately, I’ve determined it’s not possible to do it without using execute. Why it doesn’t work By examining the ActiveRecord source, we can find the code for create_table: In schema_statements.rb: def create_table(table_name, options={}) … table_definition.primary_key(options[:primary_key] || Base.get_primary_key(table_name.to_s.singularize)) unless options[:id] == false … end So we can see that when you try to specify a primary … Read more
Just a few reasons for using surrogate keys: Stability: Changing a key because of a business or natural need will negatively affect related tables. Surrogate keys rarely, if ever, need to be changed because there is no meaning tied to the value. Convention: Allows you to have a standardized Primary Key column naming convention rather … Read more
You make a good point that you can avoid some number of joined queries by using what’s called a natural key instead of a surrogate key. Only you can assess if the benefit of this is significant in your application. That is, you can measure the queries in your application that are the most important … Read more
I would use a composite (multi-column) key. CREATE TABLE INFO ( t1ID INT, t2ID INT, PRIMARY KEY (t1ID, t2ID) ) This way you can have t1ID and t2ID as foreign keys pointing to their respective tables as well.
A Table can have a Composite Primary Key which is a primary key made from two or more columns. For example: CREATE TABLE userdata ( userid INT, userdataid INT, info char(200), primary key (userid, userdataid) ); Update: Here is a link with a more detailed description of composite primary keys.
GUIDs may seem to be a natural choice for your primary key – and if you really must, you could probably argue to use it for the PRIMARY KEY of the table. What I’d strongly recommend not to do is use the GUID column as the clustering key, which SQL Server does by default, unless … Read more