英文:
Struggling with a foreign key constraint
问题
我在定义一些外键时遇到了问题。
我有一个发行者（Issuer），每个发行者有一个地址（Address）。
我有一个地址（Address），每个地址有一个国家（Country）。
我试图在这些POJO上使用Liquibase定义外键约束，但是没有成功，仍然出现相同的错误：
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 {
// ...（略去其他代码）
}
@Entity
@Table(name = "ADDRESS")
@SequenceGenerator(name = "seq_address", sequenceName = "SEQ_ADDRESS", allocationSize = 1)
public class Address {
// ...（略去其他代码）
}
@Entity
@Table(name = "ISSUER")
@SequenceGenerator(name = "seq_issuer", sequenceName = "SEQ_ISSUER", allocationSize = 1)
public class Issuer {
// ...（略去其他代码）
}
<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">
<!-- ...（略去其他代码） -->
</createTable>
<!-- ...（略去其他代码） -->
</changeSet>
<changeSet id="sremy" author="DEV-194-03">
<createTable tableName="ADDRESS">
<!-- ...（略去其他代码） -->
</createTable>
<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">
<!-- ...（略去其他代码） -->
</createTable>
<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">
<!-- ...（略去其他代码） -->
</addColumn>
<addForeignKeyConstraint
baseTableName="ASSET" baseColumnNames="ISSUER_ID"
referencedTableName="ISSUER" referencedColumnNames="ID"
constraintName="FK_ASSET_TO_ISSUER"/>
</changeSet>
如果有人遇到了相同的问题，或者在这里看到了什么问题，请不要犹豫！感谢，Stephanie
<details>
<summary>英文:</summary>
I do have a problem defining some foreign keys.
I have an Issuer, who have one address.
I have an Address who have one country.
I'm trying to define my foreign keys constraints with liquibse on those POJO, but with no luck, still same error:
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)]
Here are my classes
@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>
</databaseChangeLog>
If someone had the same problem, or see something awful in here, don't hesitate!
Thanks, Stephanie
</details>
# 答案1
**得分**: 0
首先，首先，在ADDRESS表中您没有列名COUNTRY_ID，但您仍然在尝试使用此列创建外键
<addForeignKeyConstraint baseTableName="ADDRESS" baseColumnNames="COUNTRY_ID"
constraintName="FK_ADDRESS_TO_COUNTRY"
referencedTableName="COUNTRY"
referencedColumnNames="ID"/>
将基本列名从COUNTRY_ID更改为ID。
<details>
<summary>英文:</summary>
First of all you don't have Column name COUNTRY_ID in ADDRESS table but you are still trying to create foreign key using this column
<addForeignKeyConstraint baseTableName="ADDRESS" baseColumnNames="COUNTRY_ID"
constraintName="FK_ADDRESS_TO_COUNTRY"
referencedTableName="COUNTRY"
referencedColumnNames="ID"/>
change the base column name from COUNTRY_ID to ID
</details>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论