oracle資料庫(ORA-02270:此列表的唯一或主鍵不匹配)

self_control發表於2014-12-16
Oracle資料庫
在給表加外來鍵引用時，報出 ORA-02270:此列表的唯一或主鍵不匹配。

點選(此處)摺疊或開啟

  1. /*==============================================================*/
  2. /* Table: QX_GWYH */
  3. /*==============================================================*/
  4. create table QX_GWYH
  5. (
  6.    ID VARCHAR2(32),
  7.    RY_DM VARCHAR2(11),
  8.    GW_DM VARCHAR2(32)
  9. );

  11. comment on table QX_GWYH is
  12. \'崗位使用者表\';

  14. comment on column QX_GWYH.ID is
  15. \'主鍵\';

  17. comment on column QX_GWYH.RY_DM is
  18. \'人員程式碼\';

  20. comment on column QX_GWYH.GW_DM is
  21. \'崗位程式碼\';

  23. /*==============================================================*/
  24. /* Table: QX_RY */
  25. /*==============================================================*/
  26. create table QX_RY
  27. (
  28.    RY_DM VARCHAR2(11) not null,
  29.    RY_MC VARCHAR2(100),
  30.    constraint PK_QX_RY primary key (RY_DM)
  31. );

  33. comment on table QX_RY is
  34. \'人員表\';

  36. comment on column QX_RY.RY_DM is
  37. \'人員程式碼\';

  39. comment on column QX_RY.RY_MC is
  40. \'人員名稱\';

  42. /*==============================================================*/
  43. /* Table: QX_XTYH */
  44. /*==============================================================*/
  45. create table QX_XTYH
  46. (
  47.    DLZH VARCHAR2(50) not null,
  48.    RY_DM VARCHAR2(11),
  49.    PASSWORD VARCHAR2(50),
  50.    constraint PK_QX_XTYH primary key (DLZH)
  51. );

  53. comment on table QX_XTYH is
  54. \'使用者表\';

  56. comment on column QX_XTYH.DLZH is
  57. \'登入賬號\';

  59. comment on column QX_XTYH.RY_DM is
  60. \'人員程式碼\';

  62. comment on column QX_XTYH.PASSWORD is
  63. \'登入密碼\';



  67.    
  68. alter table QX_GWYH
  69.    add constraint FK_QX_GWYH_REFERENCE_QX_XTYH foreign key (RY_DM)
  70.       references QX_XTYH (RY_DM);

  72. alter table QX_XTYH
  73.    add constraint FK_QX_XTYH_REFERENCE_QX_RY foreign key (RY_DM)
  74.       references QX_RY (RY_DM);
問題在於 引用表的欄位沒用唯一約束。

點選(此處)摺疊或開啟

  1. alter table QX_XTYH
  2.    add constraint UK_QX_XTYH_RYDM unique (RY_DM)

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/30066956/viewspace-1369791/，如需轉載，請註明出處，否則將追究法律責任。

相關文章