Oracle:查找主键——PK和NN

I am working with a database and was told the Table USERS has a single Primary Key - USERID. Using ALL_CONS_COLS gives me the following:

我正在处理一个数据库，有人告诉我表用户只有一个主键USERID。使用ALL_CONS_COLS给了我以下信息:

OWNER   | TABLE_NAME | CONSTRAINT_NAME | COLUMN_NAME | POSITION
----------------------------------------------------------------
MONSTER |   USERS    |   USER_ID_PK    |   USERREF   |   2
MONSTER |   USERS    |   USER_ID_NN    |   USERID
MONSTER |   USERS    |   USER_ID_PK    |   USERID    |   1

This suggests the PK is a composite comprised of: USERREF and USERID.

这说明PK是由USERREF和USERID组成的组合。

There are a few things I do not follow:

有几件事我不明白:

Why does USERID have both a NOT NULL constraint and also a PK constraint? PK by definition means NN. Secondly, as USERID has a NN constraint, why does USERREF not have a NN constraint? Thirdly, why is CONSTRAINT_NAME "suggesting" the PK is USER_ID (USERID), i.e., why is the constraint named after a single column; wouldn't USERS_PK be a better constraint name?

为什么USERID既具有非空约束，又有一个PK约束?PK的定义是NN。其次，由于USERID有NN约束，为什么USERREF没有NN约束?第三，为什么CONSTRAINT_NAME "表示PK为USER_ID (USERID)，即。，为什么约束以单个列命名;USERS_PK不是更好的约束名吗?

So, ultimately, is it a Composite PK or not?

那么，最终，它是一个复合PK还是不是?

1 个解决方案

#1

So, ultimately, is it a Composite PK or not?

那么，最终，它是一个复合PK还是不是?

Yes. The primary key is defined on two columns together. The position will tell you the leading column. USER_ID_PK primary key constraint is defined on USERID as leading column followed by USERREF.

是的。主键一起定义在两列上。这个职位会告诉你最重要的一栏。USER_ID_PK主键约束在USERID上定义为前导列，后跟USERREF。

Why does USERID have both a NOT NULL constraint and also a PK constraint?

为什么USERID同时具有非空约束和PK约束?

Because someone created a NOT NULL constraint on the primary key column explicitly.

因为有人显式地在主键列上创建了NOT NULL约束。

Let's test and see.

让我们来测试看看。

Single primary key

单一的主键

SQL> create table t(a number primary key);

Table created.

SQL> SELECT a.table_name,
  2    b.column_name,
  3    a.constraint_type,
  4    b.position
  5  FROM user_constraints a
  6  JOIN user_cons_columns b
  7  ON a.owner             = b.owner
  8  AND a.constraint_name  = b.constraint_name
  9  AND a.table_name       = b.table_name
 10  AND a.constraint_type IN ('P', 'C');

TABLE_NAME COLUMN_NAM C   POSITION
---------- ---------- - ----------
T          A          P          1

Composite primary key

复合主键

SQL> drop table t purge;

Table dropped.

SQL> create table t(a number, b number);

Table created.

SQL> alter table t add constraint t_pk PRIMARY KEY(a,

Table altered.

SQL> SELECT a.table_name,
  2    b.column_name,
  3    a.constraint_type,
  4    b.position
  5  FROM user_constraints a
  6  JOIN user_cons_columns b
  7  ON a.owner             = b.owner
  8  AND a.constraint_name  = b.constraint_name
  9  AND a.table_name       = b.table_name
 10  AND a.constraint_type IN ('P', 'C');

TABLE_NAME COLUMN_NAM C   POSITION
---------- ---------- - ----------
T          A          P          1
T          B          P          2

Primary key and NOT NULL

主键，非空

SQL> drop table t purge;

Table dropped.

SQL> create table t(a number primary key not null);

Table created.

SQL> SELECT a.table_name,
  2    b.column_name,
  3    a.constraint_type,
  4    b.position
  5  FROM user_constraints a
  6  JOIN user_cons_columns b
  7  ON a.owner             = b.owner
  8  AND a.constraint_name  = b.constraint_name
  9  AND a.table_name       = b.table_name
 10  AND a.constraint_type IN ('P', 'C');

TABLE_NAME COLUMN_NAM C   POSITION
---------- ---------- - ----------
T          A          C
T          A          P          1

#1