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!
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.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments