[geraldisaacs]

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?

[derrickleggett]

??? 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).

[geraldisaacs]

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
ADD CHECK (EXISTS(SELECT cust_id FROM reg_cust WHERE
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---