与外键约束有困难。

huangapple
go评论103阅读模式
英文:

Struggling with a foreign key constraint

问题

  1. 我在定义一些外键时遇到了问题。
  2. 我有一个发行者（Issuer），每个发行者有一个地址（Address）。
  3. 我有一个地址（Address），每个地址有一个国家（Country）。
  5. 我试图在这些POJO上使用Liquibase定义外键约束，但是没有成功，仍然出现相同的错误：
  7. Caused by: liquibase.exception.DatabaseException: Column "COUNTRY_ID" not found; SQL statement:
  8. 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)]
  10. 这是我的类：
  12. @Entity
  13. @Table(name = "COUNTRY")
  14. @SequenceGenerator(name = "seq_country", sequenceName = "SEQ_COUNTRY", allocationSize = 1)
  15. public class Country {
  16.     // ...（略去其他代码）
  17. }
  19. @Entity
  20. @Table(name = "ADDRESS")
  21. @SequenceGenerator(name = "seq_address", sequenceName = "SEQ_ADDRESS", allocationSize = 1)
  22. public class Address {
  23.     // ...（略去其他代码）
  24. }
  26. @Entity
  27. @Table(name = "ISSUER")
  28. @SequenceGenerator(name = "seq_issuer", sequenceName = "SEQ_ISSUER", allocationSize = 1)
  29. public class Issuer {
  30.     // ...（略去其他代码）
  31. }
  33. <changeSet id="sremy" author="DEV-194-01">
  34.     <dropColumn tableName="ASSET" columnName="LEGAL_ADDRESS"/>
  35.     <dropColumn tableName="ASSET" columnName="MAIL_ADDRESS"/>
  36.     <dropColumn tableName="ASSET" columnName="COMPANY_CONTACT_REF"/>
  37.     <dropColumn tableName="ASSET" columnName="OTHER_CONTACT_REF"/>
  38.     <dropColumn tableName="ASSET" columnName="COMPANY"/>
  39.     <dropColumn tableName="ASSET" columnName="COUNTRY"/>
  40. </changeSet>
  41. <changeSet id="sremy" author="DEV-194-02">
  42.     <createTable tableName="COUNTRY">
  43.         <!-- ...（略去其他代码） -->
  44.     </createTable>
  45.     <!-- ...（略去其他代码） -->
  46. </changeSet>
  47. <changeSet id="sremy" author="DEV-194-03">
  48.     <createTable tableName="ADDRESS">
  49.         <!-- ...（略去其他代码） -->
  50.     </createTable>
  51.     <addForeignKeyConstraint baseTableName="ADDRESS" baseColumnNames="COUNTRY_ID"
  52.                              constraintName="FK_ADDRESS_TO_COUNTRY" referencedTableName="COUNTRY"
  53.                              referencedColumnNames="ID"/>
  54. </changeSet>
  55. <changeSet author="sremy" id="DEV-194-04">
  56.     <createTable tableName="ISSUER">
  57.         <!-- ...（略去其他代码） -->
  58.     </createTable>
  59.     <addForeignKeyConstraint baseTableName="ISSUER" baseColumnNames="ADDRESS_ID"
  60.                              constraintName="FK_ISSUER_TO_ADDRESS" referencedTableName="ADDRESS"
  61.                              referencedColumnNames="ID"/>
  62. </changeSet>
  63. <changeSet id="sremy" author="DEV-194-05">
  64.     <addColumn tableName="ASSET">
  65.         <!-- ...（略去其他代码） -->
  66.     </addColumn>
  67.     <addForeignKeyConstraint
  68.             baseTableName="ASSET" baseColumnNames="ISSUER_ID"
  69.             referencedTableName="ISSUER" referencedColumnNames="ID"
  70.             constraintName="FK_ASSET_TO_ISSUER"/>
  71. </changeSet>

如果有人遇到了相同的问题，或者在这里看到了什么问题，请不要犹豫！感谢，Stephanie

  2. <details>
  3. <summary>英文:</summary>
  4. I do have a problem defining some foreign keys. 
  5. I have an Issuer, who have one address. 
  6. I have an Address who have one country.
  7. I&#39;m trying to define my foreign keys constraints with liquibse on those POJO, but with no luck, still same error: 
  8. Caused by: liquibase.exception.DatabaseException: Column &quot;COUNTRY_ID&quot; not found; SQL statement:
  9. 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)]
  10. Here are my classes 
  11. @Entity
  12. @Table(name = &quot;COUNTRY&quot;)
  13. @SequenceGenerator(name = &quot;seq_country&quot;, sequenceName = &quot;SEQ_COUNTRY&quot;, allocationSize = 1)
  14. public class Country {
  15. @Id
  16. @Column(name = &quot;ID&quot;)
  17. @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;seq_country&quot;)
  18. private Long id;
  19. @Column(name = &quot;SHORT_NAME&quot;)
  20. private String shortName;
  21. @Column(name = &quot;NAME&quot;)
  22. private String name;
  23. @OneToMany(mappedBy = &quot;country&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  24. private List&lt;Address&gt; addresses;
  25. public Long getId() {
  26. return id;
  27. }
  28. public void setId(Long id) {
  29. this.id = id;
  30. }
  31. public String getShortName() {
  32. return shortName;
  33. }
  34. public void setShortName(String shortName) {
  35. this.shortName = shortName;
  36. }
  37. public String getName() {
  38. return name;
  39. }
  40. public void setName(String name) {
  41. this.name = name;
  42. }
  43. public List&lt;Address&gt; getAddresses() {
  44. return addresses;
  45. }
  46. public void setAddresses(List&lt;Address&gt; addresses) {
  47. this.addresses = addresses;
  48. }
  49. ----------
  50. @Entity
  51. @Table(name = &quot;ADDRESS&quot;)
  52. @SequenceGenerator(name = &quot;seq_address&quot;, sequenceName = &quot;SEQ_ADDRESS&quot;, allocationSize = 1)
  53. public class Address {
  54. @Id
  55. @Column(name = &quot;ID&quot;)
  56. @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;seq_address&quot;)
  57. private Long id;
  58. @Column(name = &quot;ADDRESS&quot;)
  59. private String address;
  60. @Column(name = &quot;POSTAL_CODE&quot;)
  61. private String postalCode;
  62. @Column(name = &quot;CITY&quot;)
  63. private String city;
  64. @ManyToOne(cascade = CascadeType.ALL)
  65. @JoinColumn(name = &quot;COUNTRY_ID&quot;)
  66. private Country country;
  67. @OneToOne(mappedBy = &quot;address&quot;)
  68. private Issuer issuer;
  69. public Long getId() {
  70. return id;
  71. }
  72. public void setId(Long id) {
  73. this.id = id;
  74. }
  75. public String getAddress() {
  76. return address;
  77. }
  78. public void setAddress(String address) {
  79. this.address = address;
  80. }
  81. public String getPostalCode() {
  82. return postalCode;
  83. }
  84. public void setPostalCode(String postalCode) {
  85. this.postalCode = postalCode;
  86. }
  87. public String getCity() {
  88. return city;
  89. }
  90. public void setCity(String city) {
  91. this.city = city;
  92. }
  93. public Country getCountry() {
  94. return country;
  95. }
  96. public void setCountry(Country country) {
  97. this.country = country;
  98. }
  99. public Issuer getIssuer() {
  100. return issuer;
  101. }
  102. public void setIssuer(Issuer issuer) {
  103. this.issuer = issuer;
  104. }
  105. ----------
  106. @Entity
  107. @Table(name = &quot;ISSUER&quot;)
  108. @SequenceGenerator(name = &quot;seq_issuer&quot;, sequenceName = &quot;SEQ_ISSUER&quot;, allocationSize = 1)
  109. public class Issuer {
  110. @Id
  111. @Column(name = &quot;ID&quot;)
  112. @GeneratedValue(strategy = GenerationType.SEQUENCE, generator = &quot;seq_issuer&quot;)
  113. private Long id;
  114. @Column(name = &quot;NAME&quot;)
  115. private String name;
  116. @OneToOne(cascade = CascadeType.ALL)
  117. @JoinColumn(name = &quot;ADDRESS_ID&quot;)
  118. private Address address;
  119. @Column(name = &quot;MAIL_ADDRESS&quot;)
  120. private String emailAddress;
  121. @Column(name = &quot;COMPANY_CONTACT_REF&quot;)
  122. private String contactPerson;
  123. @Column(name = &quot;OTHER_CONTACT_REF&quot;)
  124. private String otherContactPerson;
  125. @OneToMany(mappedBy = &quot;issuer&quot;, fetch = FetchType.EAGER, cascade = CascadeType.ALL)
  126. private Collection&lt;Asset&gt; assets;
  127. public Long getId() {
  128. return id;
  129. }
  130. public void setId(Long id) {
  131. this.id = id;
  132. }
  133. public String getName() {
  134. return name;
  135. }
  136. public void setName(String name) {
  137. this.name = name;
  138. }
  139. public String getEmailAddress() {
  140. return emailAddress;
  141. }
  142. public void setEmailAddress(String emailAddress) {
  143. this.emailAddress = emailAddress;
  144. }
  145. public String getContactPerson() {
  146. return contactPerson;
  147. }
  148. public void setContactPerson(String contactPerson) {
  149. this.contactPerson = contactPerson;
  150. }
  151. public String getOtherContactPerson() {
  152. return otherContactPerson;
  153. }
  154. public void setOtherContactPerson(String otherContactPerson) {
  155. this.otherContactPerson = otherContactPerson;
  156. }
  157. public Collection&lt;Asset&gt; getAssets() {
  158. return assets;
  159. }
  160. public void setAssets(Collection&lt;Asset&gt; assets) {
  161. this.assets = assets;
  162. }
  163. public Address getAddress() {
  164. return address;
  165. }
  166. public void setAddress(Address address) {
  167. this.address = address;
  168. }
  169. }
  170. ----------
  171. &lt;changeSet id=&quot;sremy&quot; author=&quot;DEV-194-01&quot;&gt;
  172. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;LEGAL_ADDRESS&quot;/&gt;
  173. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;MAIL_ADDRESS&quot;/&gt;
  174. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;COMPANY_CONTACT_REF&quot;/&gt;
  175. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;OTHER_CONTACT_REF&quot;/&gt;
  176. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;COMPANY&quot;/&gt;
  177. &lt;dropColumn tableName=&quot;ASSET&quot; columnName=&quot;COUNTRY&quot;/&gt;
  178. &lt;/changeSet&gt;
  179. &lt;changeSet id=&quot;sremy&quot; author=&quot;DEV-194-02&quot;&gt;
  180. &lt;createTable tableName=&quot;COUNTRY&quot;&gt;
  181. &lt;column name=&quot;ID&quot; type=&quot;BIGINT&quot;&gt;
  182. &lt;constraints nullable=&quot;false&quot;/&gt;
  183. &lt;/column&gt;
  184. &lt;column name=&quot;CODE_ALPHA_2&quot; type=&quot;VARCHAR(2)&quot;/&gt;
  185. &lt;column name=&quot;NAME&quot; type=&quot;VARCHAR(50)&quot;/&gt;
  186. &lt;/createTable&gt;
  187. &lt;addPrimaryKey tableName=&quot;COUNTRY&quot; columnNames=&quot;ID&quot;
  188. constraintName=&quot;PK_COUNTRY&quot;/&gt;
  189. &lt;createSequence sequenceName=&quot;SEQ_COUNTRY&quot;/&gt;
  190. &lt;/changeSet&gt;
  191. &lt;changeSet id=&quot;sremy&quot; author=&quot;DEV-194-03&quot;&gt;
  192. &lt;createTable tableName=&quot;ADDRESS&quot;&gt;
  193. &lt;column name=&quot;ID&quot; type=&quot;BIGINT&quot;&gt;
  194. &lt;constraints nullable=&quot;false&quot;/&gt;
  195. &lt;/column&gt;
  196. &lt;column name=&quot;ADDRESS&quot; type=&quot;VARCHAR(250)&quot;/&gt;
  197. &lt;column name=&quot;POSTAL_CODE&quot; type=&quot;VARCHAR(20)&quot;/&gt;
  198. &lt;column name=&quot;CITY&quot; type=&quot;VARCHAR(250)&quot;/&gt;
  199. &lt;/createTable&gt;
  200. &lt;addPrimaryKey tableName=&quot;ADDRESS&quot; columnNames=&quot;ID&quot;
  201. constraintName=&quot;PK_ADDRESS&quot;/&gt;
  202. &lt;createSequence sequenceName=&quot;SEQ_ADDRESS&quot;/&gt;
  203. &lt;addForeignKeyConstraint baseTableName=&quot;ADDRESS&quot; baseColumnNames=&quot;COUNTRY_ID&quot;
  204. constraintName=&quot;FK_ADDRESS_TO_COUNTRY&quot; referencedTableName=&quot;COUNTRY&quot;
  205. referencedColumnNames=&quot;ID&quot;/&gt;
  206. &lt;/changeSet&gt;
  207. &lt;changeSet author=&quot;sremy&quot; id=&quot;DEV-194-04&quot;&gt;
  208. &lt;createTable tableName=&quot;ISSUER&quot;&gt;
  209. &lt;column name=&quot;ID&quot; type=&quot;BIGINT&quot;&gt;
  210. &lt;constraints nullable=&quot;false&quot;/&gt;
  211. &lt;/column&gt;
  212. &lt;column name=&quot;NAME&quot; type=&quot;VARCHAR(250)&quot;&gt;
  213. &lt;/column&gt;
  214. &lt;column name=&quot;MAIL_ADDRESS&quot; type=&quot;VARCHAR(250)&quot;&gt;
  215. &lt;/column&gt;
  216. &lt;column name=&quot;COMPANY_CONTACT_REF&quot; type=&quot;VARCHAR(250)&quot;&gt;
  217. &lt;/column&gt;
  218. &lt;column name=&quot;OTHER_CONTACT_REF&quot; type=&quot;VARCHAR(250)&quot;&gt;
  219. &lt;/column&gt;
  220. &lt;/createTable&gt;
  221. &lt;addPrimaryKey tableName=&quot;ISSUER&quot; columnNames=&quot;ID&quot;
  222. constraintName=&quot;PK_ISSUER&quot;/&gt;
  223. &lt;createSequence sequenceName=&quot;SEQ_ISSUER&quot;/&gt;
  224. &lt;addForeignKeyConstraint baseTableName=&quot;ISSUER&quot; baseColumnNames=&quot;ADDRESS_ID&quot;
  225. constraintName=&quot;FK_ISSUER_TO_ADDRESS&quot; referencedTableName=&quot;ADDRESS&quot;
  226. referencedColumnNames=&quot;ID&quot;/&gt;
  227. &lt;/changeSet&gt;
  228. &lt;changeSet id=&quot;sremy&quot; author=&quot;DEV-194-05&quot;&gt;
  229. &lt;addColumn tableName=&quot;ASSET&quot;&gt;
  230. &lt;column name=&quot;ISSUER_ID&quot; type=&quot;BIGINT&quot;&gt;
  231. &lt;constraints nullable=&quot;false&quot;/&gt;
  232. &lt;/column&gt;
  233. &lt;/addColumn&gt;
  234. &lt;addForeignKeyConstraint
  235. baseTableName=&quot;ASSET&quot; baseColumnNames=&quot;ISSUER_ID&quot;
  236. referencedTableName=&quot;ISSUER&quot; referencedColumnNames=&quot;ID&quot;
  237. constraintName=&quot;FK_ASSET_TO_ISSUER&quot;/&gt;
  238. &lt;/changeSet&gt;
  239. &lt;/databaseChangeLog&gt;
  240. If someone had the same problem, or see something awful in here, don&#39;t hesitate! 
  241. Thanks, Stephanie
  242. </details>
  243. # 答案1
  244. **得分**: 0
  245. 首先，首先，在ADDRESS表中您没有列名COUNTRY_ID，但您仍然在尝试使用此列创建外键
  1. &lt;addForeignKeyConstraint baseTableName=&quot;ADDRESS&quot; baseColumnNames=&quot;COUNTRY_ID&quot;
  2. constraintName=&quot;FK_ADDRESS_TO_COUNTRY&quot; 
  3. referencedTableName=&quot;COUNTRY&quot;
  4. referencedColumnNames=&quot;ID&quot;/&gt;
  2. 将基本列名从COUNTRY_ID更改为ID。
  3. <details>
  4. <summary>英文:</summary>
  5. First of all you don&#39;t have Column name COUNTRY_ID in ADDRESS table but you are still trying to create foreign key using this column
  1. &lt;addForeignKeyConstraint baseTableName=&quot;ADDRESS&quot; baseColumnNames=&quot;COUNTRY_ID&quot;
  2. constraintName=&quot;FK_ADDRESS_TO_COUNTRY&quot; 
  3. referencedTableName=&quot;COUNTRY&quot;
  4. referencedColumnNames=&quot;ID&quot;/&gt;
  1. change the base column name from COUNTRY_ID to ID 
  2. </details>

huangapple
  • 本文由 发表于 2020年9月8日 21:57:44
  • 转载请务必保留本文链接：https://go.coder-hub.com/63795512.html
  • java
  • jpa
  • liquibase
  • sql
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定