foreign key to "some rows" of a second table
<html>
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8">
</head>
<body bgcolor="#FFFFFF" text="#000000">
Hi All,<br>
<br>
So, I have a table that looks like this:<br>
<br>
<meta charset="utf-8">
CREATE TABLE config (<br>
region varchar(10),<br>
name varchar(10),<br>
value varchar(40)<br>
);<br>
<br>
Another looks like this:<br>
<br>
CREATE TABLE tag (<br>
host varchar(10),<br>
type varchar(10),<br>
value varchar(10)<br>
);<br>
<br>
What's the best way to set up a constraint on the 'config' table
such that the 'region' column can only contain values that exist in
the 'tag' table's value column where the 'type' is 'region'?<br>
<br>
cheers,<br>
<br>
Chris<br>
</body>
</html>
On 22/02/2016 13:03, Chris Withers wrote:
Hi All,
So, I have a table that looks like this:
CREATE TABLE config (
region varchar(10),
name varchar(10),
value varchar(40)
);Another looks like this:
CREATE TABLE tag (
host varchar(10),
type varchar(10),
value varchar(10)
);What's the best way to set up a constraint on the 'config' table such that the 'region' column can only contain values that exist in the 'tag' table's value column where the 'type' is 'region'?
Hi,
that's the reason CONSTRAINT TRIGGERS were introduced in PostgreSQL, I guess.
Just write an AFTER INSERT OR UPDATE TRIGGER ON config,
which checks for integrity.
cheers,
Chris
--
Achilleas Mantzios
IT DEV Lead
IT DEPT
Dynacom Tankers Mgmt