防止错误的数据输入(Preventing bad data input)
将数据验证完全委托给数据库引擎约束是否好的做法?
从应用程序验证数据不会阻止从其他软件无效插入(可能由另一个团队用另一种语言编写)。 使用数据库约束可以减少需要担心无效输入数据的点。
如果您在数据库和应用程序中进行验证,则维护变得无聊,因为您必须更新谁知道有多少应用程序的代码,从而增加了发生人为错误的可能性。
我只是看不到这个做的很好,看着自由软件项目的代码。
Is it good practice to delegate data validation entirely to the database engine constraints?
Validating data from the application doesn't prevent invalid insertion from another software (possibly written in another language by another team). Using database constraints you reduce the points where you need to worry about invalid input data.
If you put validation both in database and application, maintenance becomes boring, because you have to update code for who knows how many applications, increasing the probability of human errors.
I just don't see this being done very much, looking at code from free software projects.
原文:https://stackoverflow.com/questions/88962
最满意答案
我最后查看了在
Update-Database -TargetMigration MigrationName
语句末尾使用-Verbose
关键字执行的SQL语句来解决此问题。我从每个表中删除了所有外键和主键,然后使用正确的名称将它们添加回来。
entity-framework
试图这样做:DropForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles"); DropIndex("Intranet.AspNetUserRoles", new[] { "UserId" }); DropPrimaryKey("Intranet.AspNetRoles");
哪个翻译成这些
SQL
语句:IF object_id(N'[Intranet].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL ALTER TABLE [Intranet].[AspNetUserRoles] DROP CONSTRAINT [FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId] IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) DROP INDEX [IX_UserId] ON [Intranet].[AspNetUserRoles] ALTER TABLE [Intranet].[AspNetRoles] DROP CONSTRAINT [PK_Intranet.AspNetRoles]
我这样做了:
IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]
因此,要更改单个约束名称,您最终会在迁移中使用此名称:
Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" + "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]"); AddPrimaryKey("Intranet.AspNetUserRoles", "Id"); CreateIndex("Intranet.AspNetUserRoles", "UserId"); AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true);
“Intranet”是新的模式名称,“dbo”是旧的模式名称,“Id”是列名,“AspNetUserRoles”是带有外键的表,“AspNetRoles”是带有主键的表(你试图改变的那个)。
要处理
Down()
迁移方法,只需反转模式名称即可。这是我新的SchemaChange迁移:
public override void Up() { MoveTable(name: "dbo.AspNetRoles", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserRoles", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUsers", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserClaims", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserLogins", newSchema: "Intranet"); Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] \r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] \r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_RoleId] ON[Intranet].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserClaims]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserClaims] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserLogins]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserLogins]\r\n" + "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]\r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[PK_dbo.AspNetUserRoles]\r\n" + "ALTER TABLE[Intranet].[AspNetUsers] DROP CONSTRAINT[PK_dbo.AspNetUsers]\r\n" + "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[PK_dbo.AspNetUserLogins]\r\n" + "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[PK_dbo.AspNetUserClaims]"); AddPrimaryKey("Intranet.AspNetRoles", "Id"); AddPrimaryKey("Intranet.AspNetUserRoles", new[] { "UserId", "RoleId" }); AddPrimaryKey("Intranet.AspNetUsers", "Id"); AddPrimaryKey("Intranet.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" }); AddPrimaryKey("Intranet.AspNetUserClaims", "Id"); CreateIndex("Intranet.AspNetUserRoles", "UserId"); CreateIndex("Intranet.AspNetUserRoles", "RoleId"); CreateIndex("Intranet.AspNetUserClaims", "UserId"); CreateIndex("Intranet.AspNetUserLogins", "UserId"); AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserClaims", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserLogins", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserRoles", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); } public override void Down() { MoveTable(name: "Intranet.AspNetUserLogins", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUserClaims", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUsers", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUserRoles", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetRoles", newSchema: "dbo"); Sql("IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]\r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId] \r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId] \r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_RoleId] ON[dbo].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserClaims]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserClaims] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserLogins]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserLogins]\r\n" + "ALTER TABLE[dbo].[AspNetRoles] DROP CONSTRAINT[PK_Intranet.AspNetRoles]\r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[PK_Intranet.AspNetUserRoles]\r\n" + "ALTER TABLE[dbo].[AspNetUsers] DROP CONSTRAINT[PK_Intranet.AspNetUsers]\r\n" + "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[PK_Intranet.AspNetUserLogins]\r\n" + "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[PK_Intranet.AspNetUserClaims]"); AddPrimaryKey("dbo.AspNetRoles", "Id"); AddPrimaryKey("dbo.AspNetUserRoles", new[] { "UserId", "RoleId" }); AddPrimaryKey("dbo.AspNetUsers", "Id"); AddPrimaryKey("dbo.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" }); AddPrimaryKey("dbo.AspNetUserClaims", "Id"); CreateIndex("dbo.AspNetUserRoles", "UserId"); CreateIndex("dbo.AspNetUserRoles", "RoleId"); CreateIndex("dbo.AspNetUserClaims", "UserId"); CreateIndex("dbo.AspNetUserLogins", "UserId"); AddForeignKey("dbo.AspNetUserRoles", "RoleId", "dbo.AspNetRoles", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); }
I ended up looking at the SQL statements that were being executed using the
-Verbose
keyword at the end of myUpdate-Database -TargetMigration MigrationName
statements to solve this.I removed all foreign and primary keys from each table, then added them back with the correct names.
Where
entity-framework
was trying to do this:DropForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles"); DropIndex("Intranet.AspNetUserRoles", new[] { "UserId" }); DropPrimaryKey("Intranet.AspNetRoles");
Which translated to these
SQL
statements:IF object_id(N'[Intranet].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL ALTER TABLE [Intranet].[AspNetUserRoles] DROP CONSTRAINT [FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId] IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) DROP INDEX [IX_UserId] ON [Intranet].[AspNetUserRoles] ALTER TABLE [Intranet].[AspNetRoles] DROP CONSTRAINT [PK_Intranet.AspNetRoles]
I did this:
IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId] IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]
So, to change a single constraint name you would end up with this in your migration:
Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" + "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]"); AddPrimaryKey("Intranet.AspNetUserRoles", "Id"); CreateIndex("Intranet.AspNetUserRoles", "UserId"); AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true);
Where "Intranet" is your new schema name, "dbo" is the old schema name, "Id" is the column name, "AspNetUserRoles" is the table with the foreign key, and "AspNetRoles" is your table with the primary key (the one you were trying to change in the first place).
To take care of the
Down()
migration method, just reverse the schema names.Here is my new SchemaChange migration:
public override void Up() { MoveTable(name: "dbo.AspNetRoles", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserRoles", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUsers", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserClaims", newSchema: "Intranet"); MoveTable(name: "dbo.AspNetUserLogins", newSchema: "Intranet"); Sql("IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetRoles_RoleId]\r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[FK_dbo.AspNetUserClaims_dbo.AspNetUsers_UserId] \r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[FK_dbo.AspNetUserLogins_dbo.AspNetUsers_UserId] \r\n" + "IF object_id(N'[Intranet].[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[FK_dbo.AspNetUserRoles_dbo.AspNetUsers_UserId] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[Intranet].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_RoleId] ON[Intranet].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserClaims]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserClaims] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[Intranet].[AspNetUserLogins]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[Intranet].[AspNetUserLogins]\r\n" + "ALTER TABLE[Intranet].[AspNetRoles] DROP CONSTRAINT[PK_dbo.AspNetRoles]\r\n" + "ALTER TABLE[Intranet].[AspNetUserRoles] DROP CONSTRAINT[PK_dbo.AspNetUserRoles]\r\n" + "ALTER TABLE[Intranet].[AspNetUsers] DROP CONSTRAINT[PK_dbo.AspNetUsers]\r\n" + "ALTER TABLE[Intranet].[AspNetUserLogins] DROP CONSTRAINT[PK_dbo.AspNetUserLogins]\r\n" + "ALTER TABLE[Intranet].[AspNetUserClaims] DROP CONSTRAINT[PK_dbo.AspNetUserClaims]"); AddPrimaryKey("Intranet.AspNetRoles", "Id"); AddPrimaryKey("Intranet.AspNetUserRoles", new[] { "UserId", "RoleId" }); AddPrimaryKey("Intranet.AspNetUsers", "Id"); AddPrimaryKey("Intranet.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" }); AddPrimaryKey("Intranet.AspNetUserClaims", "Id"); CreateIndex("Intranet.AspNetUserRoles", "UserId"); CreateIndex("Intranet.AspNetUserRoles", "RoleId"); CreateIndex("Intranet.AspNetUserClaims", "UserId"); CreateIndex("Intranet.AspNetUserLogins", "UserId"); AddForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserClaims", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserLogins", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("Intranet.AspNetUserRoles", "UserId", "Intranet.AspNetUsers", "Id", cascadeDelete: true); } public override void Down() { MoveTable(name: "Intranet.AspNetUserLogins", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUserClaims", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUsers", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetUserRoles", newSchema: "dbo"); MoveTable(name: "Intranet.AspNetRoles", newSchema: "dbo"); Sql("IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetRoles_RoleId]\r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[FK_Intranet.AspNetUserClaims_Intranet.AspNetUsers_UserId] \r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[FK_Intranet.AspNetUserLogins_Intranet.AspNetUsers_UserId] \r\n" + "IF object_id(N'[dbo].[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId]', N'F') IS NOT NULL \r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[FK_Intranet.AspNetUserRoles_Intranet.AspNetUsers_UserId] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_RoleId' AND object_id = object_id(N'[dbo].[AspNetUserRoles]', N'U')) \r\n" + "DROP INDEX[IX_RoleId] ON[dbo].[AspNetUserRoles] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserClaims]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserClaims] \r\n" + "IF EXISTS (SELECT name FROM sys.indexes WHERE name = N'IX_UserId' AND object_id = object_id(N'[dbo].[AspNetUserLogins]', N'U')) \r\n" + "DROP INDEX[IX_UserId] ON[dbo].[AspNetUserLogins]\r\n" + "ALTER TABLE[dbo].[AspNetRoles] DROP CONSTRAINT[PK_Intranet.AspNetRoles]\r\n" + "ALTER TABLE[dbo].[AspNetUserRoles] DROP CONSTRAINT[PK_Intranet.AspNetUserRoles]\r\n" + "ALTER TABLE[dbo].[AspNetUsers] DROP CONSTRAINT[PK_Intranet.AspNetUsers]\r\n" + "ALTER TABLE[dbo].[AspNetUserLogins] DROP CONSTRAINT[PK_Intranet.AspNetUserLogins]\r\n" + "ALTER TABLE[dbo].[AspNetUserClaims] DROP CONSTRAINT[PK_Intranet.AspNetUserClaims]"); AddPrimaryKey("dbo.AspNetRoles", "Id"); AddPrimaryKey("dbo.AspNetUserRoles", new[] { "UserId", "RoleId" }); AddPrimaryKey("dbo.AspNetUsers", "Id"); AddPrimaryKey("dbo.AspNetUserLogins", new[] { "LoginProvider", "ProviderKey", "UserId" }); AddPrimaryKey("dbo.AspNetUserClaims", "Id"); CreateIndex("dbo.AspNetUserRoles", "UserId"); CreateIndex("dbo.AspNetUserRoles", "RoleId"); CreateIndex("dbo.AspNetUserClaims", "UserId"); CreateIndex("dbo.AspNetUserLogins", "UserId"); AddForeignKey("dbo.AspNetUserRoles", "RoleId", "dbo.AspNetRoles", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserClaims", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserLogins", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); AddForeignKey("dbo.AspNetUserRoles", "UserId", "dbo.AspNetUsers", "Id", cascadeDelete: true); }
相关问答
更多-
该手册非常清楚(“ tableconstraint:此表单使用与CREATE TABLE相同的语法为表添加新约束 ”) 所以你可以简单地运行: ALTER TABLE example ADD UNIQUE (a, b); The manual is pretty clear about this ("tableconstraint: This form adds a new constraint to a table using the same syntax as CREATE TABLE.") So ...
-
add_index :people, [:firstname, :lastname, :dob], :unique => true add_index :people, [:firstname, :lastname, :dob], :unique => true
-
Rails迁移:删除约束(Rails Migration: Remove constraint)[2022-07-15]
不确定你可以叫t.address ? 反正我会用这样的change_column change_column :users, :address, :string, :null => true 文件... http://apidock.com/rails/ActiveRecord/ConnectionAdapters/SchemaStatements/change_column Not sure you can call t.address? Anyway... I would use change_col ... -
如果看起来你正在击中25253号机票中描述的错误。 If looks like you are hitting the bug described in ticket 25253.
-
在fluentNhibernate自动映射更改中更改约束名称(change the constraint name in fluentNhibernate auto mapping alteration)[2023-07-12]
我没有用这个问题对每个类进行覆盖,而是创建了一个映射约定 ,它会在外键上超过30个字符的命名方案截断对象,有很多很多关系: public class OracleIHasManyConvention : IHasManyConvention { public void Apply(IOneToManyCollectionInstance instance) { var keyName = string.Format(CultureInfo.InvariantCulture, ... -
实际上,我自己就找到了答案。 将以下内容从mytable迁移移至新迁移: // add FK $table ->foreign( 'foreigntable_id' ) ->references( 'id' ) ->on( 'foreigntable' ); 由于新迁移将在迁移mytable之后以及迁移之后运行,因此在添加外键约束时将存在两个表。 因此它有效。 Actually, I just found the answer myself. Move the following ...
-
我最后查看了在Update-Database -TargetMigration MigrationName语句末尾使用-Verbose关键字执行的SQL语句来解决此问题。 我从每个表中删除了所有外键和主键,然后使用正确的名称将它们添加回来。 entity-framework试图这样做: DropForeignKey("Intranet.AspNetUserRoles", "RoleId", "Intranet.AspNetRoles"); DropIndex("Intranet.AspNetUserRole ...
-
如何为Sequel add_unique_constraint迁移添加向下块(How to add a down block for Sequel add_unique_constraint migration)[2023-06-19]
根据文档 : Sequel.migration do up do alter_table(:files) do add_unique_constraint [:name, :folder] end end down do alter_table(:files) do drop_constraint(:your_constraint_name, :type=>:unique) end end end 您必须找出唯一性约束的名称。 它应 ... -
laravel迁移生成的外键约束的名称是什么(what is the name of foreign key constraint generated by laravel migration)[2023-03-31]
您可以在表KEY_COLUMN_USAGE中的INFORMATION_SCHEMA中找到外键 例如,要查找表news外键: SELECT * FROM information_schema.KEY_COLUMN_USAGE where TABLE_NAME = 'news'; 外键的名称位于名为CONSTRAINT_NAME的列中 You can find foreign keys in the INFORMATION_SCHEMA in the table KEY_COLUMN_USAGE For exa ... -
添加not null约束时,还需要在现有记录不符合新条件时添加default值。 定义可能是这样的: type User struct { gorm.Model Name string `gorm:"not null;default:'fillertext'"` } 正如@putu所说, ALTER TABLE肯定会起作用,但你仍然需要最初添加一个默认值,以确保不合格的行符合新的要求。 完成此操作后,您可以根据需要删除默认值,并且迁移将从该点开始按预期工作。 When addi ...