May 06 2022 06:05 AM
CREATE TABLE Patient
(
taxCode VARCHAR(16) NOT NULL PRIMARY KEY,
name VARCHAR(32) NOT NULL,
surname VARCHAR(64) NOT NULL,
username VARCHAR(16) NOT NULL UNIQUE,
password VARCHAR(16) NOT NULL,
email VARCHAR(32) NOT NULL UNIQUE,
address VARCHAR(64) NOT NULL,
gender SHORT NOT NULL,
birthDate DATETIME NOT NULL,
adminCode VARCHAR(16) NOT NULL,
CONSTRAINT FKPatientToAdmin FOREIGN KEY (adminCode) REFERENCES Admin ON UPDATE CASCADE ON DELETE SET NULL
)
This statement looks pretty ok to me, given I based it on access' own documentation. Still, it won't work, giving me a CONSTRAINT error and highlighting UPDATE while giving it.
Is someone able to tell me why Access rejects this?
This is my Admin table I already created:
CREATE TABLE Admin
(
adminCode VARCHAR(16) NOT NULL PRIMARY KEY,
username VARCHAR(16) NOT NULL UNIQUE,
password VARCHAR(16) NOT NULL,
reference VARCHAR(16) NOT NULL UNIQUE,
email VARCHAR(32) NOT NULL UNIQUE
)
May 06 2022 07:19 AM
May 06 2022 01:21 PM - edited May 06 2022 01:25 PM
I believe that the ON UPDATE CASCADE and ON DELETE CASCADE statements are not supported by DAO in Access.
See https://msdn.microsoft.com/en-us/library/office/ff836971.aspx
You may be able to do this using ADO
Or see alternative code at Microsoft Access tips: DAO Programming Code Examples (allenbrowne.com)
May 07 2022 01:08 AM