Обсуждение: help with table constraint / check

hello,
This is my first post to the list, so if I make any mistakes, bear with me.  I
have a table that has the follow attributes:


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'::charactervarying))
 
Foreign Key constraints:  $1 FOREIGN KEY (parent_id) REFERENCES extranet_filesystem(fs_id) ON UPDATE
CASCADE ON DELETE CASCADE


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

On Sat, Mar 15, 2003 at 07:38:20 -0800, cliff@cliffmeyers.com wrote:
> 
> 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!

One way to do this is to add a column with the parent's file system
type (constrained to be 'dir') and then make the foreign key to the parent use
both the parent ID and the file system type.