addForeignKeyConstraint + liquibase在maven pom中使用(addForeignKeyConstraint + liquibase using in maven

addForeignKeyConstraint + liquibase在maven pom中使用(addForeignKeyConstraint + liquibase using in maven pom)

我需要使用liquibase创建下面的表但是低于错误。 有人可以帮助我找出这个有什么问题吗？ 我是DB＆liquibase的新手。

<changeSet author="mohan" id="table-for-users-roles"> <createTable tableName="USERS"> <column name="ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USERS"/> </column> <column name="NAME" type="VARCHAR2(10)" remarks="User name"></column> <column name="PASSWORD" type="VARCHAR2(10)" remarks="Password"></column> </createTable> <createTable tableName="ROLES"> <column name="ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_ROLES"/> </column> <column name="NAME" type="VARCHAR2(10)" remarks="Role name"></column> </createTable> <createTable tableName="USER_ROLES"> <column name="USER_ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USER_ROLES"/> </column> <column name="ROLE_ID" type="NUMBER(19,0)"> <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USER_ROLES"/> </column> </createTable> <addForeignKeyConstraint constraintName="FK_USERS" baseColumnNames="ID" baseTableName="USERS" referencedTableName="USER_ROLES" referencedColumnNames="USER_ID" /> <addForeignKeyConstraint constraintName="FK_ROLES" baseColumnNames="ID" baseTableName="ROLES" referencedTableName="USER_ROLES" referencedColumnNames="ROLE_ID" /> </changeSet>

这是错误：

SEVERE 2/10/16 10:32 AM: liquibase: change-logs/wm-db-changelog-master.xml: change-logs/1.0/wm-db-changelog-1.0.xml::table-for-users-roles::mohan: Change Set change-logs/1.0/wm-db-changelog-1.0.xml::table-for-users-roles::mohan failed. Error: ORA-02270: no matching unique or primary key for this column-list [Failed SQL: ALTER TABLE USERS ADD CONSTRAINT FK_USERS FOREIGN KEY (ID) REFERENCES USER_ROLES (USER_ID)] liquibase.exception.DatabaseException: ORA-02270: no matching unique or primary key for this column-list [Failed SQL: ALTER TABLE USERS ADD CONSTRAINT FK_USERS FOREIGN KEY (ID) REFERENCES USER_ROLES (USER_ID)] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301)

提前致谢！

I need to create below tables using liquibase but getting below error. Can someone help me to identify what is wrong in this? I am new to DB & liquibase.

<changeSet author="mohan" id="table-for-users-roles"> <createTable tableName="USERS"> <column name="ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USERS"/> </column> <column name="NAME" type="VARCHAR2(10)" remarks="User name"></column> <column name="PASSWORD" type="VARCHAR2(10)" remarks="Password"></column> </createTable> <createTable tableName="ROLES"> <column name="ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_ROLES"/> </column> <column name="NAME" type="VARCHAR2(10)" remarks="Role name"></column> </createTable> <createTable tableName="USER_ROLES"> <column name="USER_ID" type="NUMBER(19,0)" > <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USER_ROLES"/> </column> <column name="ROLE_ID" type="NUMBER(19,0)"> <constraints nullable="false" primaryKey="true" primaryKeyName="PK_USER_ROLES"/> </column> </createTable> <addForeignKeyConstraint constraintName="FK_USERS" baseColumnNames="ID" baseTableName="USERS" referencedTableName="USER_ROLES" referencedColumnNames="USER_ID" /> <addForeignKeyConstraint constraintName="FK_ROLES" baseColumnNames="ID" baseTableName="ROLES" referencedTableName="USER_ROLES" referencedColumnNames="ROLE_ID" /> </changeSet>

Here is the error:

SEVERE 2/10/16 10:32 AM: liquibase: change-logs/wm-db-changelog-master.xml: change-logs/1.0/wm-db-changelog-1.0.xml::table-for-users-roles::mohan: Change Set change-logs/1.0/wm-db-changelog-1.0.xml::table-for-users-roles::mohan failed. Error: ORA-02270: no matching unique or primary key for this column-list [Failed SQL: ALTER TABLE USERS ADD CONSTRAINT FK_USERS FOREIGN KEY (ID) REFERENCES USER_ROLES (USER_ID)] liquibase.exception.DatabaseException: ORA-02270: no matching unique or primary key for this column-list [Failed SQL: ALTER TABLE USERS ADD CONSTRAINT FK_USERS FOREIGN KEY (ID) REFERENCES USER_ROLES (USER_ID)] at liquibase.executor.jvm.JdbcExecutor$ExecuteStatementCallback.doInStatement(JdbcExecutor.java:301)

Thanks in advance!

最满意答案

而不是在单个变更集中运行所有这些更改，而是将它们分成至少两个变更集。 具体来说，我会在第二个变更集中将有关USERS和ROLES表的更改分开。

Rather than running all these changes in a single changeset, separate them into at least two changesets. Specifically, I would separate the changes relating the USERS and ROLES tables to each other in a second changeset.