Results 1 to 3 of 3
  1. #1
    Join Date
    May 2004
    Southampton UK

    Unanswered: Registered Customers

    This problem involves a company that only sells goods to customers that are registered

    Two tables are concerned
    1. reg_cust cust_id CHAR(6) PRIMARY KEY
    2. sale_tran cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER

    A constraint must be applied so that before a record is appended to the sale_tran table a check is made to see if the customer is registered in the reg_cust table. If the customer is not registered then the sale is aborted.

    One or more SQL statements need to be written to apply the constraint then to check that it is working

    Has anyone got any ideas?

  2. #2
    Join Date
    Apr 2004
    Kansas City, MO
    ??? You don't need to do anything for this except create the foreign key constraint. At that point, they will only be able to enter a sale_tran if there is a reg_cust. There is no checking to do. It just works (unless of course you have certain flavors of mySQL).
    When life gives you a lemon, fire the DBA.

  3. #3
    Join Date
    May 2004
    Southampton UK


    1 ----- Create the reg_cust table ----

    CREATE TABLE reg_cust( cust_id CHAR(6) PRIMARY KEY);

    2 ----- Populate reg_cust ----

    INSERT INTO reg_cust VALUES('ABC123');
    INSERT INTO reg_cust VALUES('DEF456');
    INSERT INTO reg_cust VALUES('GHI123');
    INSERT INTO reg_cust VALUES('JKL456');

    3 ----- Display the table ----

    SELECT * FROM reg_cust;

    4 ----- So far so good, so create the sale_tran table----

    CREATE TABLE sale_tran (cust_id CHAR(6) REFERENCES reg_cust, sale_date DATE, inv_no INTEGER);

    5 ----- Populate sale_tran ----

    INSERT INTO sale_tran VALUES('DEF456', DATE('2004-06-15'), 200406123);
    INSERT INTO sale_tran VALUES('GHI123', DATE('2004-06-15'), 200406124);
    SELECT * FROM sale_tran;

    6 ----- This is the point where the constraint was requested ----
    The check could have been included at point 4 but what was needed was an alteration to an existing table

    ALTER TABLE sale_tran
    cust_id = sale_tran.cust_id));

    7 ----- Alteration was successful - try an invalid entry ----

    INSERT INTO sale_tran VALUES('XYZ456', DATE('2004-06-15'), 200406125);

    8 ----- Constraint works, following message is given ----

    SQLSTATE 23000
    [Sybase][ODBC Driver] Integrity constraint violation: Invalid value for column 'cust_id' in table 'sale_tran'

    9 ----- Try another valid value ----

    INSERT INTO sale_tran VALUES('JKL456', DATE('2004-06-15'), 200406126);
    SELECT * FROM sale_tran;

    10 ----- Table is displayed as expected –Task completed---

Posting Permissions

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