On Thursday 05 Jun 2003 3:55 pm, [EMAIL PROTECTED] wrote:
> hi ,
>
> Is there any way to enforce fkeys only on subset of
> the table something on the lines of unique partial indexes
I'm afraid not (AFAIK). What might be a solution in your case is to define
another unique index. For example, you might have (row_id) as your primary
key with another unique index on (row_id,row_type). You could then have a
foreign-key that referenced those two columns.
> or any work around ? (on update or insert trigger is the only thing i can
> think of)
Yep, you'll need to build some triggers of your own. The techdocs guides
section is down at the moment, but see my brief example in the attachment.
It would be really useful to be able to have something like:
CREATE contract (
con_id SERIAL,
con_type varchar(4),
...
PRIMARY KEY (con_id)
);
CREATE tel_con_section (
tcs_id SERIAL,
tcs_con_ref int4,
...
CONSTRAINT contract_fk FOREIGN KEY (tcs_con_ref,'TEL') REFERENCES contract
(con_id,con_type)
);
or even:
FOREIGN KEY (tcs_con_ref) REFERENCES contract (con_id) WHERE
contract.con_type='TEL'
Is there a developer around who could comment how plausible this would be?
--
Richard HuxtonA Brief Real-world Trigger Example
Created 2003-03-13 by Richard Huxton ([EMAIL PROTECTED])
Version: First Draft - treat with caution
This is a real-world example, showing how you can use the plpgsql procedural
language to build a trigger function to enforce integrity beyond that which
foreign keys can offer you. The tables have been simplified to the minimum
required for this example, but represent real tables for a real project.
The Database
- We have a set of products, each of which has a certain type.
- We have a set of servers, each of which can only serve a specific type of product.
- Servers provide a subset of products and the same product can be available from
many servers.
This gives us a table structure like:
product (pr_id, pr_type)
server (svr_id, pr_type)
server_products (svr_id, pr_id)
We can use "foreign keys":#references to make sure that 'server_products' have a
valid
'svr_id' and 'pr_id' but if we want to enforce the type of a product we need
to check *two* tables, because what matters is that the 'pr_type' from
'product' matches the corresponding one in 'server'.
Solution 1 (in an ideal world...)
A simple solution would be to define a view 'possible_server_products' that
would contain 'svr_id','pr_type' and 'pr_id' and then reference that.
Unfortunately, PostgreSQL can't check a foreign key against a view, only
against a real table. This isn't a theoretical limitation of relational theory,
but there are some complex implementation issues, so it isn't likely to
happen any time soon.
I would describe how to implement foreign keys against views, but there
isn't space in the margin here ;-).
Solution 2 (well, it' not normal...)
If we change our definitions slightly, so we have 'server_products
(svr_id,pr_type,pr_id)'
we can have a foreign key referencing '(svr_id,pr_type)' in table 'server' and
another on '(pr_type,pr_id)' in 'product' that does exactly what we want.
Unfortunately, we now need to look up the 'pr_type' in our application when
we insert a new product. We can avoid that by defining a view that looked like
our original version of 'server_products' and write rules that do the lookup for us.
There is however, a more fundamental problem with this solution - we have a
redundant 'pr_type' in every row of 'server_products'. Is it part of the primary
key for this table, or if not does it depend on the primary key? Well, our
primary key is clearly '(svr_id,pr_id)' since this identifies the row. But -
'pr_type' doesn't depend on this key, it depends on 'svr_id' alone (or 'pr_id'
alone, depending on how you want to look at it). This is a violation of 2nd
Normal Form ("2NF":#references) and I like a normalised database, so this
solution isn't acceptable.
Solution 3 (here's one I made earlier...)
So - we don't want to change our table definitions but do want to enforce
product type. To do this we will need to manually add three triggers (one for
each table involved) and a function or functions to enforce our constraints.
In this case, I chose to have one function used by all three triggers. You
could make a good argument for three different functions, but having all the
code in one place makes it less likely I'll forget to change something if I
change the database structure.
Assuming we've run the "createlang":#references utility, we'll define our
function using:
CREATE OR REPLACE FUNCTION check_srv_prodtype() RETURNS trigger AS '
...code here...
' LANGUAGE 'plpgsql';
Within the function we'll need to check the value of the 'TG_RELNAME'
pseudo-variable which tells us which table triggered a call to us. Then, we can
check the contents of the 'NEW' pseudo-record to see if the values are
acceptable. If they
