1Z0-051 QUESTION 36 create table中约束应注意的地方

最新推荐文章于 2025-05-02 22:38:33 发布

create_oracle

最新推荐文章于 2025-05-02 22:38:33 发布

阅读量585

点赞数

CC 4.0 BY-SA版权

分类专栏： OCP之路

本文链接：https://blog.youkuaiyun.com/create_oracle/article/details/25572869

OCP之路专栏收录该内容

37 篇文章

订阅专栏

本文详细解析了在执行SQL命令时出现的错误情况，通过实例展示了如何正确理解并解决SQL命令执行过程中的常见问题。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

36. Examine the following SQL commands:
SQL>CREATE TABLE products (
prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
prod_name CHAR(30),
prod_qty NUMBER(6),
CONSTRAINT p_name NOT NULL,
CONSTRAINT prod_pk PRIMARY KEY (prod_id));
SQL>CREATE TABLE warehouse (
warehouse_id NUMBER(4),
roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
location VARCHAR2(25),
prod_id NUMBER(3),
CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),
CONSTRAINT prod_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id));
Which statement is true regarding the execution of the above SQL commands?
A. Both commands execute successfully.
B. The first CREATE TABLE command generates an error because the NULL constraint is not valid.
C. The second CREATE TABLE command generates an error because the CHECK constraint is not valid.
D. The first CREATE TABLE command generates an error because CHECK and PRIMARY KEY
constraints cannot be used for the same column.
E. The first CREATE TABLE command generates an error because the column PROD_ID cannot be used
in the PRIMARY KEY and FOREIGN KEY constraints.

Answer: B

解析：

A选项错，因为第一句执行会报错；

B选项对。如果注释掉CONSTRAINT p_name NOT NULL，执行成功；

C选项错误，第二句可以顺利执行；

D选项错误，语句中的check和Primary key可以同时存在，只要prod_id唯一就可以；

E选项错误，prod_id可以是主键也可以是外键。

SQL> CREATE TABLE products (
  2  prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
  3  prod_name CHAR(30),
  4  prod_qty NUMBER(6),
  5  CONSTRAINT p_name NOT NULL,
  6  CONSTRAINT prod_pk PRIMARY KEY (prod_id));
CONSTRAINT p_name NOT NULL,
                  *
ERROR at line 5:
ORA-00904: : invalid identifier


SQL> CREATE TABLE products (
  2  prod_id NUMBER(3) CONSTRAINT p_ck CHECK (prod_id > 0),
  3  prod_name CHAR(30),
  4  prod_qty NUMBER(6),
  5  --CONSTRAINT p_name NOT NULL,
  6  CONSTRAINT prod_pk PRIMARY KEY (prod_id));

Table created.

SQL> CREATE TABLE warehouse (
  2  warehouse_id NUMBER(4),
  3  roomno NUMBER(10) CONSTRAINT r_id CHECK(roomno BETWEEN 101 AND 200),
  4  location VARCHAR2(25),
  5  prod_id NUMBER(3),
  6  CONSTRAINT wr_pr_pk PRIMARY KEY (warehouse_id,prod_id),
  7  CONSTRAINT prod_fk FOREIGN KEY (prod_id) REFERENCES products(prod_id));

Table created.