pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedhelp with table constraint / check
2+ messages / 2 participants
[nested] [flat]
* help with table constraint / check
@ 2003-03-15 15:38 cliff@cliffmeyers.com
0 siblings, 1 reply; 2+ messages in thread
From: cliff@cliffmeyers.com @ 2003-03-15 15:38 UTC (permalink / raw)
To: 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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: help with table constraint / check
@ 2003-03-15 16:29 Bruno Wolff III <bruno@wolff.to>
parent: cliff@cliffmeyers.com
0 siblings, 0 replies; 2+ messages in thread
From: Bruno Wolff III @ 2003-03-15 16:29 UTC (permalink / raw)
To: cliff@cliffmeyers.com; +Cc: pgsql-sql
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.
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2003-03-15 16:29 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2003-03-15 15:38 help with table constraint / check cliff@cliffmeyers.com
2003-03-15 16:29 ` Bruno Wolff III <bruno@wolff.to>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox