I am taking a practice test for the

[missmis44]

I am taking a practice test for the Intro to Oracle exam. Why is the answer: Line 1

Evaluate this CREATE TABLE statement, which line of this statement will cause an error?

1. CREATE TABLE order (
2. id# NUMBER(9) CONSTRAINT PRIMARY KEY,
3. order$ NUMBER(9) CONSTRAINT NOT NULL,
4. date_1 DATE DEFAULT SYSDATE,
5. Column2 VARCHAR2(5),
6. "cust_id" NUMBER(9));

 

[missmis44]

Is it because the word 'ORDER' is an invalid table name? Is this why I cannot create a table called 'ORDER'?

 

[sybaseguru]

Hi,

"Order" is a reserved word in Oracle and amny other RDBMS. It is actually part of ANSI SQL. Try calling your table order1 and that "part" will work. You do not need keywords constraint for primary key and not null (lines 2 and 3 respectively). The correct syntax is

SQL> CREATE TABLE order1 (
  2  id# NUMBER(9) primary key, -- do not need constraint
  3  order$ NUMBER(9) NOT NULL, -- do not need constraint
  4  date_1 DATE DEFAULT SYSDATE,
  5  Column2 VARCHAR2(5),
  6  "cust_id" NUMBER(9))
  7  ;


Table created.

SQL> desc order1;

SQL> desc order1;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID#                                       NOT NULL NUMBER(9)
 ORDER$                                    NOT NULL NUMBER(9)
 DATE_1                                             DATE
 COLUMN2                                            VARCHAR2(5)
 cust_id                                            NUMBER(9)

SQL>

Hope this helps and good luck

 

[ddrillich]

Sysbaseguru,

Why don’t we need to specify the primary key constraint?
Does Oracle do it for us?

Thanks,
Dan

 

[sybaseguru]

Dan,

In this particular case as we did not name the constraint Oracle created one for us.

SQL> create table sales (
  2  stor_id         char(4)        not null primary key);

Table created.

SQL> select constraint_name, constraint_type from user_constraints where table_name = 'SALES';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C0018826                   C
SYS_C0018827                   P

SQL>
However we better off naming it so that it has meaningful description
SQL> create table sales (
  2  stor_id         char(4)        not null constraint sales_pk primary key);

Table created.

SQL>  select constraint_name, constraint_type from user_constraints where table_name = 'SALES';

CONSTRAINT_NAME                C
------------------------------ -
SYS_C0018828                   C
SALES_PK                       P
OR

  1  create table sales
  2  (
  3  stor_id         char(4)         not null,
  4  ord_num         varchar(20)     not null,
  5  vdate           date            not null,
  6  constraint      sales_pk
  7                  primary key (stor_id, ord_num)
  8* )
  9  /

Table created.

Note that in this case the constraint has been referenced as sales_pk with two columns. You cannot do this on every column itself.
Have a look at the following definition for a child table

create table salesdetail
(
stor_id         char(4)         not null,
ord_num         varchar(20)     not null,
title_id        int             not null        references titles(title_id), -- column level constraint
qty             smallint default 0      not null,
discount        float           not null,
constraint      sales_detail_fk_sales                   -- table level constraint
                foreign key (stor_id, ord_num)
                references sales (stor_id, ord_num),
constraint      sales_detail_pk                         -- table level constraint
                primary key (stor_id,ord_num,title_id)
)

Constraints are created in the database to enforce a business rule and to specify relationships between tables. Primary key constraints uniquely identify a row in the table and you can alter them using alter table command.

I hope this helps and good luck.