March 14th, 2003, 01:40 PM
|
|
Contributing User
|
|
Join Date: May 2001
Location: Washington DC, USA
Posts: 156
Time spent in forums: 26 m 7 sec
Reputation Power: 13
|
|
|
help with table constraint
I have a table that is defined as follows:
Code:
Column | Type | Modifiers
-----------+------------------------+------------------------------------------------------------------------
fs_id | integer | not null default nextval('public.extranet_filesystem_fs_id_seq'::text)
parent_id | integer | not null default 0
fs_name | character varying(255) | not null
fs_type | character varying(4) | not null default 'file'
Indexes: extranet_filesystem_pkey primary key btree (fs_id),
extranet_filesystem_u1 unique btree (parent_id, fs_name)
Check constraints: "extranet_filesystem_fs_type" ((fs_type = 'file'::character varying) OR (fs_type = 'dir'::character varying))
Foreign Key constraints: $1 FOREIGN KEY (parent_id) REFERENCES extranet_filesystem(fs_id) ON UPDATE CASCADE ON DELETE CASCADE
Basically, this table is mirroring a file system in a very simple way.
What I want to do is set up a constraint / check so that when a record is added, it takes the parent_id being passed in the INSERT statement, looks at the record whose fs_id equals the parent_id, and makes sure that the fs_type for that record is 'dir' and not 'file'.
Can I do this with standard constraints or do I need to use some PL/pgSQL features? Anyone have a few hints? Thanks!
-Cliff
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
