1

I am practicing SQL in MS Access for my test. Currently trying to create constraints on a table.

CREATE TABLE Container (
Nr int NOT NULL,
Soort varchar(255),
Aanschaf double NOT NULL,
Huurprijs double NOT NULL,
Verhuurder varchar(255) NOT NULL,
PRIMARY KEY (Nr),
CONSTRAINT fk_verh FOREIGN KEY (Verhuurder)
REFERENCES Verhuurbedrijf
ON UPDATE cascade
ON DELETE cascade
);

I get a syntax error on the ON UPDATE cascade. UPDATE turns black. Not sure what is wrong here?

I used this documentation as well: https://msdn.microsoft.com/en-us/library/bb177889(v=office.12).aspx

Keep in mind I have to learn how to do it programmatically through SQL.

Athylus
  • 193
  • 1
  • 1
  • 10
  • Does table.field `verhurrbedrijf.verhuurder` exist as a primary key? (what does the create table for Verhuurbedrijf look like?) if the table and key don't exist, that's the problem. that table has to be created 1st. – xQbert Jan 03 '17 at 14:54
  • CREATE TABLE Verhuurbedrijf ( Naam varchar(255) NOT NULL, Plaats varchar(255) NOT NULL, PRIMARY KEY(Naam) ); Thats my code for the table Verhuurbedrijf. Verhuurder is the field in the table container. If I leave out the on update and on delete, the table will be created. – Athylus Jan 03 '17 at 15:00
  • To start with, you are missing the primary key column referenced by Verhuurder column. – Anand Jan 03 '17 at 15:03
  • One can not create a foreign key to a non key field. If naam is the primary key, you should be referencing naam instead of verhuurder. Does `CONSTRAINT fk_verh FOREIGN KEY (naam) REFERENCES Verhuurbedrijf ON UPDATE cascade ON DELETE cascade` work instead? but I'm not sure that achieves the same desired result. – xQbert Jan 03 '17 at 15:04
  • I thought it automatically refers to the primary key field in the table Verhuurbedrijf? Am I allowed to share links to other websites here? http://i.imgur.com/iCd3CUO.png I am trying to create these four tables where the italic column names are foreign keys. – Athylus Jan 03 '17 at 15:10
  • xQbert, changing the constraint in the way you mentioned still gives me the syntaxis error on the UPDATE keyword. – Athylus Jan 03 '17 at 15:41

0 Answers0