1. Consider the following SQL statements: CREATE TABLE orders ( order_num SERIAL

Question

1. Consider the following SQL statements:

   CREATE TABLE orders (
   order_num SERIAL,
   order_date DATE,
   PRIMARY KEY (order_num) CONSTRAINT pk_orders);

   CREATE TABLE items (
   item_num SMALLINT,
   order_num INTEGER,
   FOREIGN KEY (order_num) REFERENCES orders CONSTRAINT fk_orders);

The following statement is executed:

   ALTER TABLE orders DROP order_num;

What will happen? (Choose all that apply) *This question is required.

a. The order_num column is dropped from the orders table

b. The pk_orders primary key constraint is dropped from the orders table

c. The fk_orders foreign key constraint is dropped from the items table

d. An error will occur since there is a foreign key in the items table which references the order_num column in the orders table

2. Which of the following statements applies to enabling a disabled constraint? *This question is required.

a. Existing rows are checked to see if they satisfy the constraint. If any rows do not satisfy the constraint, they are removed from the table

b. Existing rows are checked to see if they satisfy the constraint. If any rows do not satisfy the constraint, an error is returned and the constraint remains disabled

c. Existing rows are not checked to see if they satisfy the constraint

d. A disabled constraint cannot be enabled if the table contains existing rows. The rows must be unloaded, the constraint enabled, and then the rows can be loaded back into the table. If any rows do not satisfy the constraint, they will not be reloaded into the table

Explanation / Answer

1. (d) An error will occur since there is a foreign key in the items table which references the order_num column in the orders table.

Column order_num is a primary key in table orders which is being referred by table items as foreign key means Parent-child dependency. For dropping colum from parent table orders, you first have to remove the foreign key constraint in the child table items to remove dependency. It you don't it will give an error and won't drop the column the either

2.(b) While enabling a disabled constraint existing rows are checked to see if they satisfy the constraint. If any rows do not satisfy the constraint, an error is returned and the constraint remains disabled because it wouldn't satisfy the constraint requirement. For enabling a constraint, you must maintain the requirement of constraint.

For example- In the above table, after disabling fk_orders constraint, new data are inserted in items table which has null values for order_num . Now, on enabling fk_orders constraint, existed data won't satisfy the constraint integrity and error will be shown

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.