时间戳

首页 > 解决方案 > 与外键约束作斗争

java - 与外键约束作斗争

问题描述

我确实在定义一些外键时遇到了问题。我有一个发行人，他有一个地址。我有一个拥有一个国家的地址。

我正在尝试在那些 POJO 上使用 liquibse 定义我的外键约束，但没有运气，仍然是同样的错误：

Caused by: liquibase.exception.DatabaseException: Column "COUNTRY_ID" not found; SQL statement:
ALTER TABLE PUBLIC.ADDRESS ADD CONSTRAINT FK_ADDRESS_TO_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES PUBLIC.COUNTRY (ID) [42122-200] [Failed SQL: (42122) ALTER TABLE PUBLIC.ADDRESS ADD CONSTRAINT FK_ADDRESS_TO_COUNTRY FOREIGN KEY (COUNTRY_ID) REFERENCES PUBLIC.COUNTRY (ID)]

这是我的课

@Entity
@Table(name = "COUNTRY")
@SequenceGenerator(name = "seq_country", sequenceName = "SEQ_COUNTRY", allocationSize = 1)
public class Country {

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_country")
    private Long id;

    @Column(name = "SHORT_NAME")
    private String shortName;

    @Column(name = "NAME")
    private String name;

    @OneToMany(mappedBy = "country", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private List<Address> addresses;

    public Long getId() {
        return id;
    }

    public void setId(Long id) {
        this.id = id;
    }

    public String getShortName() {
        return shortName;
    }

    public void setShortName(String shortName) {
        this.shortName = shortName;
    }

    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public List<Address> getAddresses() {
        return addresses;
    }

    public void setAddresses(List<Address> addresses) {
        this.addresses = addresses;
    }

@Entity
@Table(name = "ADDRESS")
@SequenceGenerator(name = "seq_address", sequenceName = "SEQ_ADDRESS", allocationSize = 1)
public class Address {

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_address")
    private Long id;

    @Column(name = "ADDRESS")
    private String address;

    @Column(name = "POSTAL_CODE")
    private String postalCode;

    @Column(name = "CITY")
    private String city;

    @ManyToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "COUNTRY_ID")
    private Country country;

    @OneToOne(mappedBy = "address")
    private Issuer issuer;

    public Long getId() {

        return id;
    }

    public void setId(Long id) {

        this.id = id;
    }

    public String getAddress() {

        return address;
    }

    public void setAddress(String address) {

        this.address = address;
    }

    public String getPostalCode() {

        return postalCode;
    }

    public void setPostalCode(String postalCode) {

        this.postalCode = postalCode;
    }

    public String getCity() {

        return city;
    }

    public void setCity(String city) {

        this.city = city;
    }

    public Country getCountry() {
        return country;
    }

    public void setCountry(Country country) {
        this.country = country;
    }

    public Issuer getIssuer() {
        return issuer;
    }

    public void setIssuer(Issuer issuer) {
        this.issuer = issuer;
    }

@Entity
@Table(name = "ISSUER")
@SequenceGenerator(name = "seq_issuer", sequenceName = "SEQ_ISSUER", allocationSize = 1)
public class Issuer {

    @Id
    @Column(name = "ID")
    @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = "seq_issuer")
    private Long id;

    @Column(name = "NAME")
    private String name;

    @OneToOne(cascade = CascadeType.ALL)
    @JoinColumn(name = "ADDRESS_ID")
    private Address address;

    @Column(name = "MAIL_ADDRESS")
    private String emailAddress;

    @Column(name = "COMPANY_CONTACT_REF")
    private String contactPerson;

    @Column(name = "OTHER_CONTACT_REF")
    private String otherContactPerson;

    @OneToMany(mappedBy = "issuer", fetch = FetchType.EAGER, cascade = CascadeType.ALL)
    private Collection<Asset> assets;

    public Long getId() {

        return id;
    }

    public void setId(Long id) {

        this.id = id;
    }

    public String getName() {

        return name;
    }

    public void setName(String name) {

        this.name = name;
    }

    public String getEmailAddress() {

        return emailAddress;
    }

    public void setEmailAddress(String emailAddress) {

        this.emailAddress = emailAddress;
    }

    public String getContactPerson() {

        return contactPerson;
    }

    public void setContactPerson(String contactPerson) {

        this.contactPerson = contactPerson;
    }

    public String getOtherContactPerson() {

        return otherContactPerson;
    }

    public void setOtherContactPerson(String otherContactPerson) {

        this.otherContactPerson = otherContactPerson;
    }

    public Collection<Asset> getAssets() {

        return assets;
    }

    public void setAssets(Collection<Asset> assets) {

        this.assets = assets;
    }

    public Address getAddress() {

        return address;
    }

    public void setAddress(Address address) {

        this.address = address;
    }
}

<changeSet id="sremy" author="DEV-194-01">
    <dropColumn tableName="ASSET" columnName="LEGAL_ADDRESS"/>
    <dropColumn tableName="ASSET" columnName="MAIL_ADDRESS"/>
    <dropColumn tableName="ASSET" columnName="COMPANY_CONTACT_REF"/>
    <dropColumn tableName="ASSET" columnName="OTHER_CONTACT_REF"/>
    <dropColumn tableName="ASSET" columnName="COMPANY"/>
    <dropColumn tableName="ASSET" columnName="COUNTRY"/>
</changeSet>
<changeSet id="sremy" author="DEV-194-02">

    <createTable tableName="COUNTRY">
        <column name="ID" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="CODE_ALPHA_2" type="VARCHAR(2)"/>
        <column name="NAME" type="VARCHAR(50)"/>
    </createTable>

    <addPrimaryKey tableName="COUNTRY" columnNames="ID"
                   constraintName="PK_COUNTRY"/>
    <createSequence sequenceName="SEQ_COUNTRY"/>

</changeSet>

<changeSet id="sremy" author="DEV-194-03">
    <createTable tableName="ADDRESS">
        <column name="ID" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="ADDRESS" type="VARCHAR(250)"/>
        <column name="POSTAL_CODE" type="VARCHAR(20)"/>
        <column name="CITY" type="VARCHAR(250)"/>
    </createTable>

    <addPrimaryKey tableName="ADDRESS" columnNames="ID"
                   constraintName="PK_ADDRESS"/>
    <createSequence sequenceName="SEQ_ADDRESS"/>


    <addForeignKeyConstraint baseTableName="ADDRESS" baseColumnNames="COUNTRY_ID"
                             constraintName="FK_ADDRESS_TO_COUNTRY" referencedTableName="COUNTRY"
                             referencedColumnNames="ID"/>
</changeSet>

<changeSet author="sremy" id="DEV-194-04">

    <createTable tableName="ISSUER">

        <column name="ID" type="BIGINT">
            <constraints nullable="false"/>
        </column>
        <column name="NAME" type="VARCHAR(250)">
        </column>
        <column name="MAIL_ADDRESS" type="VARCHAR(250)">
        </column>
        <column name="COMPANY_CONTACT_REF" type="VARCHAR(250)">
        </column>
        <column name="OTHER_CONTACT_REF" type="VARCHAR(250)">
        </column>
    </createTable>

    <addPrimaryKey tableName="ISSUER" columnNames="ID"
                   constraintName="PK_ISSUER"/>
    <createSequence sequenceName="SEQ_ISSUER"/>

    <addForeignKeyConstraint baseTableName="ISSUER" baseColumnNames="ADDRESS_ID"
                             constraintName="FK_ISSUER_TO_ADDRESS" referencedTableName="ADDRESS"
                             referencedColumnNames="ID"/>

</changeSet>

<changeSet id="sremy" author="DEV-194-05">
    <addColumn tableName="ASSET">
        <column name="ISSUER_ID" type="BIGINT">
            <constraints nullable="false"/>
        </column>
    </addColumn>
    <addForeignKeyConstraint
            baseTableName="ASSET" baseColumnNames="ISSUER_ID"
            referencedTableName="ISSUER" referencedColumnNames="ID"
            constraintName="FK_ASSET_TO_ISSUER"/>
</changeSet>

如果有人遇到同样的问题，或者在这里看到可怕的东西，请不要犹豫！

谢谢，斯蒂芬妮

标签： javasqljpaliquibase

解决方案

首先，您在 ADDRESS 表中没有列名 COUNTRY_ID 但您仍在尝试使用此列创建外键

    <addForeignKeyConstraint baseTableName="ADDRESS" baseColumnNames="COUNTRY_ID"
                             constraintName="FK_ADDRESS_TO_COUNTRY" 
    referencedTableName="COUNTRY"
                             referencedColumnNames="ID"/>

将基列名称从 COUNTRY_ID 更改为 ID

推荐阅读