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:
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
Advertisement
Answer
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);