Search code examples
sqlms-accessms-access-2010alter-table

How to create Multiple-field foreign key in Access?

I have found this tutorial, which says that question can be solved this way:

Multiple-field constraint:

CONSTRAINT name     {PRIMARY KEY (primary1[, primary2 [, …]]) |     
UNIQUE (unique1[, unique2 [, …]]) |     
NOT NULL (notnull1[, notnull2 [, …]]) |     
FOREIGN KEY [NO INDEX] (ref1[, ref2 [, …]]) 
REFERENCES foreigntable [(foreignfield1 [, foreignfield2 [, …]])]    
[ON UPDATE CASCADE | SET NULL]     [ON DELETE CASCADE | SET NULL]};

For my simplified database:

enter image description here

I wrote such sql code, which has an idea to connect 2 columns from 2 tables. In addition, I would like to add unique constraint on 2 field of S2T table: 

alter table S2T add constraint TargetUniqKey
UNIQUE(target_table_name, target_column_name) 
Foreign key(TableName, ColumnName) references TargetColumns 
on update cascade on delete cascade;

But I get an error, saying that I have syntax error in Alter table construction, please help

Solution

  • This works as should, seems to be that official tutorial confusing:

    alter table S2T add constraint TargetUniqKey
Foreign key(target_table_name, target_column_name) 
References TargetColumns(TableName, ColumnName) ;

    Mentioned uniqueness can be achieved this way (might be helpful for creating 1 to 1 connection)

    ALTER TABLE S2T ADD 
CONSTRAINT Mykey
UNIQUE (target_table_name, target_column_name);