CockroachDB SQL开发基础 ——创建表的更多有趣示例 (1)

     上一篇文章介绍了CockroachDB中创建数据库和表的基本方法和示例。在这些示例中使用的CRDB语句的语法也能够用于其它的数据库（比如MySQL或者兼容MySQL的PingCap的TiDB）。 CockroachDB在SQL标准的遵从和实现度方面，在开源NewSQL/HTAP数据库中是比较领先的（相比较来说，TiDB在SQL标准的支持方面要稍弱一些）。

     本文将介绍在创建表时的更多示例，包括“主键”、“外键”、“Check约束”这些SQL标准中的特性用法；还有创建带有计算列和能够映射KV存储的表。CRDB也支持表分区特性（企业版支持），这将在后续文章中描述。此外，CRDB也对CREATE TABLE语句进行了扩展，能够支持“多列族定义”，这也会在后续文章中介绍。

 1. 主键和外键

  (1) 创建没有主键表

  在CRDB中，表必须具有主键。如果在创建表的时候没有指定主键，那么CRDB会 自动创建一个隐含列作为主键。这个隐含列的名称叫做rowid，它的类型是INT，其值 由unique_rowid()系统函数自动生成。

  我们看一个例子：

CREATE TABLE logon (
  user_id INT,
  logon_date DATE
);

在创建了logon表之后，执行如下语句查看列的情况：

     SHOW COLUMNS FROM logon;

由于CRDB对于主键会自动创建一个索引，所以我们执行如下语句：

     SHOW INDEX FROM logon;

由上图我们可以看到，CRDB为没有定义主键的表自动创建了主键的情况。

（2）创建具有主键和外键的表

  我们通过例子加以说明：

CREATE TABLE customers(             --创建"父表"
  id INT PRIMARY KEY,
  name VARCHAR(30)
);

CREATE TABLE orders(                    --创建"子表"
  id INT PRIMARY KEY,
  customer_id INT REFERENCES customers(id) ON DELETE CASCADE
);

 在成功创建表之后，我们执行如下语句查看表定义：

      SHOW CREATE TABLE orders

输出如下图：

    

 

2. Check约束

  CRDB支持SQL标准中的Check约束，包括字段级的约束和表级的约束。让我们两个示例：

--字段级Check约束定义

CREATE TABLE inventories (
  product_id        INT NOT NULL,
  warehouse_id      INT NOT NULL,
  quantity_on_hand  INT NOT NULL CHECK (quantity_on_hand > 0),
  PRIMARY KEY (product_id, warehouse_id)
);

--表级Check约束

CREATE TABLE inventories (
  product_id        INT NOT NULL,
  warehouse_id      INT NOT NULL,
  quantity_on_hand  INT NOT NULL,
  PRIMARY KEY (product_id, warehouse_id),
  CONSTRAINT ok_to_supply CHECK (quantity_on_hand > 0 AND warehouse_id BETWEEN 100 AND 200) 
);

3. 创建具有计算列的表

    还是看一个示例来了解CRDB的这个特性：

CREATE TABLE names( 
  id INT PRIMARY KEY,
  first_name STRING,
  last_name STRING,
  full_name STRING AS (CONCAT(first_name, ' ', last_name)) STORED
);

   在上面的示例中，fulle_name字段就是“计算列”。在向这个表插入数据时，不需 要为这个字段指定值，CRDB会自动根据计算列定义的计算规则为其生成值并存储。 当然，我们也可以通过视图来生成“计算列”，但是它不会存储。

   INSERT INTO names (id, first_name, last_name) VALUES (1, 'Lola', 'McDog'), (2, 'Carl', 'Kimball'), (3, 'Ernie', 'Narayan');

   SELECT * FROM names;

4. 创建映射KV存储的表

   CRDB是一个构建在事务化的、强一致性KV存储上的分布式NewSQL数据库。虽 然我们不能直接访问这个KV存储，你能够通过创建一个只有两个字段的表作为“映 射”，实现对KV存储的访问。我们看一个示例：

     CREATE TABLE kv (k INT PRIMARY KEY, v BYTES);

   如果这样的表没有其它索引和外键定义的话，那么在执行INSERT、UPSERT、 UPDATE、DELETE等语句时，CRDB会有最小的开销（官方文档中描述只有百分之几 的开销）。下面是一个UPSERT语句的示例：

     UPSERT INTO kv VALUES (1, b'hello')

   这种机制为开发人员提供了便捷的能够访问KV存储的SQL接口，并且能够支持事务。

 

参考链接：

     https://www.cockroachlabs.com/docs/stable/create-table.html