Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2010
    Posts
    1

    Unanswered: Help in Postgrel triggers

    Hi there,

    I need to create a trigger and I'm completely lost... I'm new to postgrel but I had some experience in mysql before.

    I have this tables:
    Code:
    CREATE TYPE type  AS ENUM ('Breakfast', 'Lunch', 'Dinner');
    
    CREATE TABLE meal (
      nmeal integer NOT NULL PRIMARY KEY,
      mealtype  type NOT NULL
    );
                    
                    
    CREATE TABLE recipe (
      nrecipe     integer NOT NULL PRIMARY KEY,
      name         varchar(30) NOT NULL,
      time        time WITHOUT TIME ZONE NOT NULL,
      recipetype  type NOT NULL,
      /* Keys */
      CONSTRAINT receita_nome
        UNIQUE (name)
    );
    
    CREATE TABLE composes (
      nmeal  integer NOT NULL,
      nrecipe   integer NOT NULL,
      amount    integer NOT NULL DEFAULT 0,
      /* Checks */
      CONSTRAINT porcoes
        CHECK (amount>=0),
      /* Foreign keys */
      CONSTRAINT refeicao
        FOREIGN KEY (nmeal)
        REFERENCES public.meal(nmeal)
        ON DELETE CASCADE
        ON UPDATE CASCADE,
      CONSTRAINT receita
        FOREIGN KEY (nrecipe)
        REFERENCES public.receita(nrecipe)
        ON DELETE CASCADE
        ON UPDATE CASCADE
    );
    I'm trying to store meals and the recepies need for that meal. The relationship must be stored in table composes.

    I need to create a trigger that only allows insertion or update in table composes if both the meal and the recipe have the same type.

    The problem is that I can't start to understand how I can do this in postgrel and I can't find any suitable example.

    Could someone give me a help??

    Thanks

  2. #2
    Join Date
    May 2008
    Posts
    277
    I have some doubts about your design, although without knowing exactly what you're trying to accomplish, it's difficult to say. For instance, many (if not most or all) foods are equally suitable for both lunch and dinner. There's also no reason why someone can't have an omelette for dinner, or a steak for breakfast.

    At the very least, I'd turn your type enum into another table. This way, if you decide to change your meal types (eg, add 'brunch'), you don't have to make structural changes to your database.

    All that aside, the easiest, most efficient, and most foolproof way to enforce your constraint is to add additional keys to meal and recipe that include the type, and use this as your foreign key reference in composes.

    Code:
    create table meal (
        ...
        unique (id, type)
    );
    
    create table recipe(
        ...
        unique (id, type)
    );
    
    create table composes (
        ...
        meal_id int not null,
        meal_type text not null,
        foreign key (meal_id, meal_type) references meal,
    
        recipe_id int not null,
        recipe_type text not null,
        foreign key (recipe_id, recipe_type) references recipe,
    
        check (meal_type = recipe_type),
        ...
    );
    You can, optionally, combine meal_type and recipe_type into one column. However, should you ever decide to allow meals and recipes of different types, the solution above only requires you to drop the check constraint.

  3. #3
    Join Date
    Jun 2004
    Location
    Arizona, USA
    Posts
    1,848
    Also, creating a type named 'type' is a very poor practice. Call it mealtype, or meals, or something similar. (or better yet, drop the type and enum and use a table named 'meals' instead, as futurity suggested.) Any time that you name an object with a SQL keyword, you are intentionally creating confusion.
    Last edited by loquin; 11-25-10 at 16:15.
    Lou
    使大吃一惊
    "Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
    "I have my standards. They may be low, but I have them!" - Bette Middler
    "It's a book about a Spanish guy named Manual. You should read it." - Dilbert


Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •