Results 1 to 4 of 4

Thread: Help!

  1. #1
    Join Date
    Oct 2003
    Posts
    4

    Red face Unanswered: Help!

    There are 3 tables...the last one needs a constraint using an attribute from a previous table...there's a foreign key in the table, but I don't know how to write the constraint ...

    CREATE TABLE Autobus
    (
    NoAutobus NUMBER CONSTRAINT pk_NoAutobus PRIMARY KEY,
    NoTypeAutobus Number,
    CONSTRAINT fk_noTypeAutobus FOREIGN KEY (NoTypeAutobus)
    REFERENCES TypeAutobus (NoTypeAutobus)
    )

    CREATE TABLE TypeAutobus
    (
    NoTypeAutobus NUMBER CONSTRAINT pk_NoTypeAutobus PRIMARY KEY,
    NbSeat NUMBER
    )

    CREATE TABLE Contract
    (
    NoContract NUMBER CONSTRAINT pk_NoContract PRIMARY KEY,
    NoAutobus NUMBER,
    NumberPassengerEstimate NUMBER,
    CONSTRAINT fk_noAutobus FOREIGN KEY (NoAutobus)
    REFERENCES Autobus (NoAutobus)
    CONSTRAINT c_NumberPassengerEstimate (NumberPassengerEstimate < ......


    The problem is....I need a constraint on the attribute NumberPassengerEstimate from the table Contract....the constraint is:
    NumberPassengerEstimate < NbSeat (from the table TypeAutobus)

    The only link between NumberPassengerEstimate and NbSeat is a foreign key (NoAutobus)

    Finally the question is how to applicate a constraint on NumberPassengerEstimate using an attribute from an other table and what's the syntax????

  2. #2
    Join Date
    Sep 2003
    Location
    The Netherlands
    Posts
    311
    hi,

    u can't use a constraint, but u have to use a before row insert and/or a before row update trigger.
    Check out the documentation for the syntax.

    Hope this helps.
    Edwin van Hattem
    OCP DBA / System analyst

  3. #3
    Join Date
    Jul 2003
    Posts
    2,296
    That is a crazy design.
    The before-insert trigger is a good move.

    Otherwise store the proper column (ie: add the column) in the contract table so that you can check on insert.
    - The_Duck
    you can lead someone to something but they will never learn anything ...

  4. #4
    Join Date
    Sep 2003
    Posts
    156

    Re: Help!

    Originally posted by oligator
    There are 3 tables...the last one needs a constraint using an attribute from a previous table...there's a foreign key in the table, but I don't know how to write the constraint ...

    CREATE TABLE Autobus
    (
    NoAutobus NUMBER CONSTRAINT pk_NoAutobus PRIMARY KEY,
    NoTypeAutobus Number,
    CONSTRAINT fk_noTypeAutobus FOREIGN KEY (NoTypeAutobus)
    REFERENCES TypeAutobus (NoTypeAutobus)
    )

    CREATE TABLE TypeAutobus
    (
    NoTypeAutobus NUMBER CONSTRAINT pk_NoTypeAutobus PRIMARY KEY,
    NbSeat NUMBER
    )

    CREATE TABLE Contract
    (
    NoContract NUMBER CONSTRAINT pk_NoContract PRIMARY KEY,
    NoAutobus NUMBER,
    NumberPassengerEstimate NUMBER,
    CONSTRAINT fk_noAutobus FOREIGN KEY (NoAutobus)
    REFERENCES Autobus (NoAutobus)
    CONSTRAINT c_NumberPassengerEstimate (NumberPassengerEstimate < ......


    The problem is....I need a constraint on the attribute NumberPassengerEstimate from the table Contract....the constraint is:
    NumberPassengerEstimate < NbSeat (from the table TypeAutobus)

    The only link between NumberPassengerEstimate and NbSeat is a foreign key (NoAutobus)

    Finally the question is how to applicate a constraint on NumberPassengerEstimate using an attribute from an other table and what's the syntax????
    the first table you create REFERENCES TypeAutobus (NoTypeAutobus) when the TypeAutoBus table has not been created... if the bus has a set number of seats you can set a max value for both NbSeat and NumberPassengerEstimate - or you can do good old error checking.
    rgs,

    Ghostman

Posting Permissions

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