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

问题描述

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

成员-------ID 名称 bandID-----------------------0 皮埃尔 11 图 3约翰二书 33 戴夫 2乐队-----身份证号码----------------1 波段 a2 波段 b3 波段 c

我想为members.bandId 和bands.ID 之间的关系添加一个cascade to null 约束

这就是我所拥有的

ALTER TABLE 成员 ADD CONSTRAINT membresBands_FKFOREIGN KEY (bandID) REFERENCE Bands(ID) ON DELETE CASCADE SET NULL

但我收到此错误消息:

CONSTRAINT 子句中的语法错误

从 msdn 我发现

CREATE TABLE 订单(订单 ID 整数主键，客户 ID 整数，OrderNotes NCHAR VARYING (255),约束 FKOrdersCustId 外键 (CustId)REFERENCES 客户 ON UPDATE SET NULL ON DELETE SET NULL

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

谢谢！

解决方案

我等了八个小时才发布这个...

使用可视化基本模块

'定义关系属性的位值.Public Const dbRelationCascadeNull As Long = &H2000公共函数 MakeRel()'目的:使用 DAO 创建 Cascade-to-Null 关系.Dim db As DAO.DatabaseDim rel As DAO.RelationDim fld As DAO.Field设置 db = CurrentDb()'CreateRelation() 的参数:任何唯一名称、主表、相关表、属性.设置 rel = db.CreateRelation("membre_bands", "bands", "membres", dbRelationCascadeNull)Set fld = rel.CreateField("ID") '主表中的字段.fld.ForeignName = "band" '相关表中的匹配字段.rel.Fields.Append fld '将字段添加到关系的字段集合中.db.Relations.Append rel '将关系添加到数据库.'报告并清理.Debug.Print rel.Attributes设置 db = 无结束函数

然后调用MakeRel函数

在 http://allenbrowne.com/ser-64.html 上找到的函数>

Using a MS Acess 2007 database accessed by vb.net applicationI have two existing table

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

I want to add an cascade to null constraint to the relation between members.bandId and bands.ID

This is what I have

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

But I get this error message:

From msdn I found

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

Is it possible to alter a table in MS Access to set relationship to cascade to null?

Thank you!

解决方案

I had to wait eight hours to post this...

Using a visual basic module

'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

then call the MakeRel function

function found on http://allenbrowne.com/ser-64.html

这篇关于MS Access 将级联到空约束设置为现有表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！