CODEWITHSUNDEEP
ms-accessforeign-keysconstraintsreferential-integrity
user7159104
user7159104

Reputation: 23

MS Access Data-Definition Query Add Referential Integrity Constraint

I am trying to write a data-definition query to add a constraint to a table that enforces referential integrity

ALTER TABLE SS_SIZE ALTER COLUMN NDS TEXT 
CONSTRAINT fk_NDS REFERENCES NDS (NDS) ON UPDATE CASCADE ON DELETE CASCADE

I've also tried

ALTER TABLE SS_SIZE ALTER COLUMN NDS 
CONSTRAINT fk_NDS_NDS FOREIGN KEY (NDS) REFERENCES NDS ON UPDATE CASCADE ON DELETE CASCADE

Neither of these will work.

Upvotes: 1

Views: 103

Answers (1)

Sergey S.
Sergey S.

Reputation: 6336

Try this:

ALTER TABLE SS_SIZE 
    ADD CONSTRAINT fk_NDS FOREIGN KEY (NDS) 
        REFERENCES NDS (NDS) ON UPDATE CASCADE ON DELETE CASCADE

Please note, that DAO doesn't support ON UPDATE CASCADE ON DELETE CASCADE options, you should use ADO for this. Query designer uses DAO, so you won't be able to execute the query with cascade options using query designer.

VBA ADO example:

CurrentProject.Connection.Execute strSQL

Upvotes: 2

Related Questions