I have found this tutorial, which says that question can be solved this way:
Multiple-field constraint:
x
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);