您想知道如何在Access 2003中使用ON UPDATE CASCADE/ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL吗?
来源:互联网 发布:淘宝店铺级别分类 编辑:程序博客网 时间:2024/06/07 17:35
您想知道如何在Access 2003中使用ON UPDATE CASCADE/ ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL。
根据我的测试,ON UPDATE CASCADE/ ON DELETE CASCADE 能够在adp 数据库和ANSI92 mdb数据库中正常使用,然而,ON UPDATE SET NULL/ ON DELETE SET NULL在adp 数据库和ANSI92 的mdb数据库中都提示语法错误。
以下的信息供您参考:
第一步:把mdb数据库设为ANSI92数据库
=========================
1. 打开mdb数据库
2. 点击Tools -> Options -> Tables/Queries -> SQL Server Compatible Syntax (ANSI92) -> This database -> OK.
第二步:在adp 或ANSI92 mdb数据库中使用ON UPDATE CASCADE/ ON DELETE CASCADE:
方法一:在Query Designer 中创建存储过程
-----------------------------------------------
1. 打开adp 数据库。
2. 点击Objects 下的Queries
3. 创建一个新的存储过程StoredProcedure1, 切换到SQL View. 输入以下代码:
Create PROCEDURE StoredProcedure1
AS
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))
RETURN
4. 创建新的存储过程StoredProcedure2, 切换到SQL View. 输入以下代码:
CREATE PROCEDURE StoredProcedure2
AS
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)
RETURN
注意: 如果是mdb 数据库,那应该创建两个新的Query.
Query1 为以下代码:
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))
Query2 为以下代码:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)
方法二:创建新的module 并输入以下代码:
------------------------------------------
Function test()
Dim sqlstr As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
sqlstr1 = " CREATE TABLE Customers1 (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))"
sqlstr2 = "CREATE TABLE Orders1 (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId1 FOREIGN KEY (custid) REFERENCES customers1 ON UPDATE CASCADE ON DELETE CASCADE )"
conn.Execute sqlstr1
conn.Execute sqlstr2
Application.RefreshDatabaseWindow
conn.Close
End Function
我将会继续测试ON UPDATE SET NULL/ ON DELETE SET NULL. 如果有新的进展, 我会尽早让您知道.
[/CHAPTER]
根据我的测试,ON UPDATE CASCADE/ ON DELETE CASCADE 能够在adp 数据库和ANSI92 mdb数据库中正常使用,然而,ON UPDATE SET NULL/ ON DELETE SET NULL在adp 数据库和ANSI92 的mdb数据库中都提示语法错误。
以下的信息供您参考:
第一步:把mdb数据库设为ANSI92数据库
================
1. 打开mdb数据库
2. 点击Tools -> Options -> Tables/Queries -> SQL Server Compatible Syntax (ANSI92) -> This database -> OK.
第二步:在adp 或ANSI92 mdb数据库中使用ON UPDATE CASCADE/ ON DELETE CASCADE:
方法一:在Query Designer 中创建存储过程
-----------------------------------------------
1. 打开adp 数据库。
2. 点击Objects 下的Queries
3. 创建一个新的存储过程StoredProcedure1, 切换到SQL View. 输入以下代码:
Create PROCEDURE StoredProcedure1
AS
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))
RETURN
4. 创建新的存储过程StoredProcedure2, 切换到SQL View. 输入以下代码:
CREATE PROCEDURE StoredProcedure2
AS
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)
RETURN
注意: 如果是mdb 数据库,那应该创建两个新的Query.
Query1 为以下代码:
CREATE TABLE Customers (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))
Query2 为以下代码:
CREATE TABLE Orders (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId FOREIGN KEY (CustId) REFERENCES Customers ON UPDATE CASCADE ON DELETE CASCADE)
方法二:创建新的module 并输入以下代码:
------------------------------------------
Function test()
Dim sqlstr As String
Dim conn As ADODB.Connection
Set conn = CurrentProject.Connection
sqlstr1 = " CREATE TABLE Customers1 (CustId INTEGER PRIMARY KEY, CLstNm NCHAR VARYING(50))"
sqlstr2 = "CREATE TABLE Orders1 (OrderId INTEGER PRIMARY KEY, CustId INTEGER, OrderNotes NCHAR VARYING (255), CONSTRAINT FKOrdersCustId1 FOREIGN KEY (custid) REFERENCES customers1 ON UPDATE CASCADE ON DELETE CASCADE )"
conn.Execute sqlstr1
conn.Execute sqlstr2
Application.RefreshDatabaseWindow
conn.Close
End Function
我将会继续测试ON UPDATE SET NULL/ ON DELETE SET NULL. 如果有新的进展, 我会尽早让您知道.
[/CHAPTER]
- 您想知道如何在Access 2003中使用ON UPDATE CASCADE/ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL吗?
- on delete set null & on delete cascade
- on delete set null & on delete cascade
- On delete cascade和on delete set null
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- oracle中使用on delete cascade和on delete set null来建立外键
- ON DELETE CASCADE ON UPDATE CASCADE
- ON UPDATE CASCADE ON DELETE CASCADE
- MySQL 设置cascade on delete on update
- oracle 建立外键约束时on delete cascade 和 on delete set null
- on delete cascade和on delete set null的级联删除
- 主外键关联删除(on delete set null和on delete cascade)
- on update cascade 和on delete cascade 的作用
- on update cascade 和on delete cascade 的区别
- 级联引用完整性约束ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT }
- 掌握 Ajax,第 1 部分: Ajax 简介
- Understanding Service Oriented Architecture[quote]
- 关于"“/”应用程序中的服务器错误。"
- Enterprise JavaBeans 3.0 EJB服务器端组件
- problem 1081
- 您想知道如何在Access 2003中使用ON UPDATE CASCADE/ON DELETE CASCADE,ON UPDATE SET NULL/ ON DELETE SET NULL吗?
- 旅行-出门必备用品清单
- C++中操作数据库的几段代码
- 转载 PHP三级连动菜单 好象..
- JDBC的数据库连接池技术研究与应用
- 在ACCESS中如何用sql语句建立删除表关系?
- GDI+中使用Image从IStream创建图像
- 激情岁月
- JPCAP——JAVA中的数据链路层控制[转]