A key is just a normal index. A way over simplification is to think of it like a card catalog at a library. It points MySQL in the right direction. A unique key is also used for improved searching speed, but it has the constraint that there can be no duplicated items (there are no … Read more
With EF6 and the DbContext API (for SQL Server), I’m currently using this piece of code: try { // Some DB access } catch (Exception ex) { HandleException(ex); } public virtual void HandleException(Exception exception) { if (exception is DbUpdateConcurrencyException concurrencyEx) { // A custom exception of yours for concurrency issues throw new ConcurrencyException(); } else … Read more
The first record will be kept, the rest deleted ยงยง: IGNORE is a MySQL extension to standard SQL. It controls how ALTER TABLE works if there are duplicates on unique keys in the new table or if warnings occur when strict mode is enabled. If IGNORE is not specified, the copy is aborted and rolled … Read more
TL;DR “Superkey” is a RM (Relational Model of Data) term. There’s no standard use in SQL. The superkeys of an SQL table might reasonably informally be said to be the column sets that you could declare primary key or unique not null, plus maybe {} when a table holds at most one row (although you … Read more
The Entity Framework 6.1 now supports uniques with both Data Annotations and Fluent API. Data Annotations (Reference) public class MyEntityClass { [Index(IsUnique = true)] [MaxLength(255)] // for code-first implementations public string MyUniqueProperty{ get; set; } } Fluent API (Reference) public class MyContext : DbContext { protected override void OnModelCreating(DbModelBuilder modelBuilder) { modelBuilder .Entity<MyEntityClass>() .Property(t => … Read more
Yes, use INSERT … ON DUPLICATE KEY UPDATE id=id (it won’t trigger row update even though id is assigned to itself). If you don’t care about errors (conversion errors, foreign key errors) and autoincrement field exhaustion (it’s incremented even if the row is not inserted due to duplicate key), then use INSERT IGNORE like this: … Read more
Specify a smaller length for your e-mail: $table->string(’email’, 250); Which is the default, actually: $table->string(’email’); And you should be good. For Laravel 5.4 you can find a solution in this Laravel 5.4: Specified key was too long error, Laravel News post: As outlined in the Migrations guide to fix this all you have to do … Read more
KEY and INDEX are synonyms in MySQL. They mean the same thing. In databases you would use indexes to improve the speed of data retrieval. An index is typically created on columns used in JOIN, WHERE, and ORDER BY clauses. Imagine you have a table called users and you want to search for all the … Read more
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
With Entity Framework 6.1, you can now do this: [Index(“IX_FirstAndSecond”, 1, IsUnique = true)] public int FirstColumn { get; set; } [Index(“IX_FirstAndSecond”, 2, IsUnique = true)] public int SecondColumn { get; set; } The second parameter in the attribute is where you can specify the order of the columns in the index. More information: MSDN