help with table constraint / check

From: cliff(at)cliffmeyers(dot)com
To: pgsql-sql(at)postgresql(dot)org
Subject: help with table constraint / check
Date: 2003-03-15 15:38:20
Message-ID: 20030315073820.3084.h016.c001.wm@mail.cliffmeyers.com.criticalpath.net
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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'::character varying))
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

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Bruno Wolff III 2003-03-15 16:29:54 Re: help with table constraint / check
Previous Message joost witteveen 2003-03-15 09:16:15 create index right after create table not working?