MS Access将级联到空约束设置为现有表

一尘不染

MS Access将级联到空约束设置为现有表

sql

使用通过vb.net应用程序访问的MS Acess 2007数据库，我有两个现有表

Members
-------
ID     name     bandID
-----------------------
0      Pierre   1
1      Chart    3
2      John     3
3      Dave     2

Bands
-----
ID     bandName
----------------
1      Band a
2      Band b
3      Band c

我想将级联添加到null约束到member.bandId和bands.ID之间的关系

这就是我所拥有的

ALTER TABLE members ADD CONSTRAINT membresBands_FK
    FOREIGN KEY (bandID) REFERENCE Bands(ID) ON DELETE CASCADE SET NULL

但我收到此错误消息：

CONSTRAINT子句中的语法错误

从msdn我发现

CREATE TABLE Orders 
  (OrderId INTEGER PRIMARY KEY, 
  CustId INTEGER, 
  OrderNotes NCHAR VARYING (255), 
  CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) 
  REFERENCES Customers ON UPDATE SET NULL ON DELETE SET NULL

是否可以更改MS Access中的表以将级联关系设置为null？

谢谢！

阅读 127

2021-05-23

共1个答案

一尘不染

我必须等待八个小时才能发布此信息…

使用视觉基本模块

'Define the bit value for the relation Attributes.
Public Const dbRelationCascadeNull As Long = &H2000

Public Function MakeRel()
    'Purpose: Create a Cascade-to-Null relation using DAO.
    Dim db As DAO.Database
    Dim rel As DAO.Relation
    Dim fld As DAO.Field

    Set db = CurrentDb()
    'Arguments for CreateRelation(): any unique name, primary table, related table, attributes.
    Set rel = db.CreateRelation("membre_bands", "bands", "membres", dbRelationCascadeNull)
    Set fld = rel.CreateField("ID")  'The field from the primary table.
    fld.ForeignName = "band"           'Matching field from the related table.
    rel.Fields.Append fld                    'Add the field to the relation's Fields collection.
    db.Relations.Append rel                  'Add the relation to the database.


    'Report and clean up.
    Debug.Print rel.Attributes
    Set db = Nothing
End Function

然后调用MakeRel函数

在 http://allenbrowne.com/ser-64.html上找到的功能

2021-05-23