Forum Discussion
Create Table statement not working in Access
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
)
- that is MSSQL syntax?
use MS Access syntax.
https://docs.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/create-table-statement-microsoft-access-sqlI 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 ADOOr see alternative code at Microsoft Access tips: DAO Programming Code Examples (allenbrowne.com)
- Yes, I found out you have to inject the query from the visualBasic editor and it works from there. Same with CHECK constraints. Thank you!
