Which CREATE TABLE statement is valid?
A.
CREATE TABLE ord_details
(ord_no NUMBER(2) PRIMARY KEY,
item_no NUMBER(3) PRIMARY KEY,
ord_date DATE NOT NULL);
B.
CREATE TABLE ord_details
(ord_no NUMBER(2) UNIQUE, NOT NULL,
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL);
C.
CREATE TABLE ord_details
(ord_no NUMBER(2) ,
item_no NUMBER(3),
ord_date DATE DEFAULT NOT NULL,
CONSTRAINT ord_uq UNIQUE (ord_no),
CONSTRAINT ord_pk PRIMARY KEY (ord_no));
D.
CREATE TABLE ord_details
(ord_no NUMBER(2),
item_no NUMBER(3),
ord_date DATE DEFAULT SYSDATE NOT NULL,
CONSTRAINT ord_pk PRIMARY KEY (ord_no, item_no));
Explanation:
PRIMARY KEY Constraint
A PRIMARY KEY constraint creates a primary key for the table. Only one primary key can be
created for each table. The PRIMARY KEY constraint is a column or a set of columns that
uniquely identifies each row in a table. This constraint enforces the uniqueness of the column or
column combination and ensures that no column that is part of the primary key can contain a null
value.
Note: Because uniqueness is part of the primary key constraint definition, the Oracle server
enforces the uniqueness by implicitly creating a unique index on the primary key column or
columns.
A is wrong a table can have only one primary key.
B is wring “Unique, not null” means primary key. Don’t need to write that way.
C.Unique (ord_no) is not needed because ord_no is a primary key.
HI i think option B is wrong not becaue of “Don’t need to write that way”.Its wrong because of the syntax
HI i think option B is wrong not becaue of “Unique, not null -Don’t need to write that way”.Its wrong because of the syntax
C is wrong because:
SQL> create table tab4 (ord_date DATE DEFAULT NOT NULL);
create table tab4 (ord_date DATE DEFAULT NOT NULL)
*
ERROR at line 1:
ORA-00936: missing expression
not null cant be set as default, but this one will work:
SQL> create table tab6 (ord_date DATE DEFAULT SYSDATE NOT NULL);
Table created.