Taurus22 Taurus22 - 1 month ago 6
SQL Question

SQL creating Table assignment

I have to create a Table for an Assignment, I have finished it but there is still a question. I don't understand what I have to do in the ii. I just wrote a VARCHAR command but I'm not sure if this is right, because there is written range instead of maximum 2 chars like the viii.
Below is the whole exercise, hope somebody can help me.


i. Agent Agent_code -- Unique PK / Non-unique PK

ii. Agent Areacode -- Valid range / Out of range

iii.Agent Phone -- Maximum 13chars / Size too large

iv. Agent Name -- NOT NULL / NULL

v. Agent YTD_Sls -- Numeric / Wrong datatype

vi. Customer Cus_Code -- Unique PK / Non-unique PK

vii.Customer LName -- NOT NULL / NULL

viii.Customer Initial -- Maximum 2chars / Size too large

ix. Customer Renew_date -- DATE datatype / Incorrect datatype x.

For example, the first business rule requires Agent_code to be a unique PK. To confirm that this business rule has been implemented you will write an INSERT command that will attempt to insert a non-unique PK. By looking at the first row of sample data below, we could attempt to insert the non-unique value of A101, which already exists in the table. We would expect to see an error message confirming that a unique or PK was violated. This would confirm that the business rule is being enforced by the database.

Answer

You need to put a check constraint on the Areacode field, so that all entries will conform to whatever the range of valid area codes are.

Then try to insert/update a row with an invalid Areacode to make sure that it fails.

Comments