#1
  1. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Washington DC, USA
    Posts
    156
    Rep 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
  2. #2
  3. Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2002
    Posts
    56
    Rep Power
    12
    You can define your own function (plpgsql or whatever) and use it in a CHECK constraint.

    http://www.ca.postgresql.org/docs/aw...k/node132.html
    Casuistry Agnostic
  4. #3
  5. No Profile Picture
    Contributing User
    Devshed Newbie (0 - 499 posts)

    Join Date
    May 2001
    Location
    Washington DC, USA
    Posts
    156
    Rep Power
    13
    Thanks, that's exactly what I wanted to know. Time to delve into PL/pgsql!


    -Cliff

IMN logo majestic logo threadwatch logo seochat tools logo