Born to Thrive

Evaluate the following SQL statement:
(Note that the numbers 2,3 etc in the SQL statement are line numbers and not part of the syntax)
SQL> CREATE TABLE product
2 (prod_id NUMBER(3),
3 prod_name VARCHAR2(25),
4 qty NUMBER(7,2),
5 price NUMBER(10,2),
6 CONSTRAINT prod_id_pk PRIMARY KEY(prod_id),
7 CONSTRAINT prod_name_uq UNIQUE (prod_name),
8 CONSTRAINT price_nn NOT NULL (price));
What is the outcome of executing this command?
A. It generates an error at line 6.
B. It generates an error at line 7.
C. It generates an error at line 8.
D. It executes successfully and creates the PRODUCTS table.

Theory:

Since the question asks about the syntax of creating a table and constrains, the information required to answer the question can be found as follows: CREATE TABLE and CONSTRAINTS.

CONSTRAINTS are used to define an integrity constraint - a rule that restricts the values in a database. Oracle Database lets you create six types of constraints.

• A NOT NULL constraint prohibits a database value from being null.
• A unique constraint prohibits multiple rows from having the same value in the same column or combination of columns but allows some values to be null.
• A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
• A foreign key constraint requires values in one table to match values in another table.
• A check constraint requires a value in the database to comply with a specified condition.
• A REF column by definition references an object in another object type or in a relational table. A REF constraint lets you further describe the relationship between the REF column and the object it references.

The constrains can be declared in two ways.

• As part of the definition of an individual column or attribute. This is called INLINE specification.
• As part of the table definition. This is called OUT-OF-LINE specification.

NOT NULL constraints must be declared inline. All other constraints can be declared either inline or out of line.

Answer:

A - Wrong - the Primary Key constraint syntax is correct
B - Wrong - the Unique constraint syntax is correct
C - Correct - the Not Null constrain cannot be declared out of line
D - Wrong - the Not Null constrain syntax is invalid, hence the query doesn't execute successfully