您想知道如何在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]