0%

C# Entity Framework Core 2.0 and PostgreSQL Provider

發表於 更新於 分類於 ，

上次用 C# Entity Framework Core 2.0 與 Oracle Provider 連結至 Oracle 資料庫。這次改用 PostgreSQL 開放式資料庫。所需要做的就是把 Oracle Provider 改用 PostgreSQL Provider。程式碼幾乎都不需要修改。

開始之前，稍微來了解一下 PostgreSQL。

PostgreSQL

PostgreSQL 自稱是世界上最先進的開源資料庫。 它是一種企業級的關連式資料庫管理系統，與最佳非開源的專有資料庫系統 Oracle，Microsoft SQL Server 和 IBM DB2 相當。 PostgreSQL 的特殊之處在於它不只是資料庫，它也是一個應用程序平台。

PostgreSQL 很快。在基準測試中，PostgreSQL 可以超過或匹配許多其他開放式和專有資料庫的性能。 PostgreSQL 允許使用多種編程語言編寫存儲過程(Stored Procedure)與涵式。 除了 C，SQL 和 PL/pgSQL 的預設包裝語言外，還可以簡單的啟用對其他語言的支援，例如 PL/Perl、PL/Python、PL/V8 (PL/JavaScript)、PL/Ruby 和 PL/R。對多種語言的支援使您可以選擇能夠最好地解決當前問題的結構的語言。

例如，使用 R 進行統計和製圖，使用 Python 調用 Web Services，使用 Python SciPY 程式庫進行科學計算，使用 PL/V8 進行數據驗證，處理字符串和處理 JSON 數據。 更簡單的是，找到所需的開源自由可用的函數，找出其編寫的語言，在 PostgreSQL 中啟用該特定語言，然後復制代碼。

近年來，我們見證了 NoSQL 資料庫的興起（儘管其中很多可能都被炒作了）。儘管 PostgreSQL 從根本上來說是關係型的，但您會發現很多處理非關係型數據的工具。 PostgreSQL 的 ltree 擴展自遠古時代就已經存在並提供圖形支援。hstore 擴展允許您存儲鍵值對 (Key/Value pairs)。JSON 和 JSONB 類型允許存儲類似於 MongoDB 的文檔。在許多方面，PostgreSQL 甚至在該術語誕生之前就已採用 NoSQL！

這裡就從 Oracle 的資料映對，來快速了解 PostgreSQL，將 Oracle 的 DEPT 與 EMP 資料表轉到 PostgreSQL。

cr_dept.sql
1
2
3
4
5
6
7
create table dept
(
  deptno numeric(4,0),
  dname  varchar(32),
  loc    varchar(32),
  constraint dept_pk primary key(deptno)
);
cr_emp.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
create table emp
(
  empno    numeric(4,0),
  ename    varchar(32),
  job      varchar(32),
  mgr      numeric(4,0),
  hiredate date,
  sal      numeric(10,2),
  comm     numeric(10,2),
  deptno   numeric(4,0),
  constraint emp_pk primary key(empno),
  constraint emp_dept_fk foreign key (deptno) references dept(deptno),
  constraint emp_manager_fk foreign key (mgr) references emp(empno)
);

create index emp_deptno_ix on emp(deptno);

DDL 語法與 Oracle 幾乎一樣，只有資料型態不同，PostgreSQL 的 date 型態只存到日期，要匹配 Oracle 的 date 型態可改用 timestamp 資料型態。

# psql 類似 Oracle Sqlplus
$ psql -V
psql (PostgreSQL) 12.3 (Ubuntu 12.3-1.pgdg20.04+1)

# 登入 PostgreSQL
$ psql -U demo -W -d db01
Password:
psql (12.3 (Ubuntu 12.3-1.pgdg20.04+1))
Type "help" for help.

db01=> \i cr_dept.sql
CREATE TABLE
db01=> \i cr_emp.sql
CREATE TABLE
CREATE INDEX
db01=> \d emp
                         Table "demo.emp"
  Column  |         Type          | Collation | Nullable | Default
----------+-----------------------+-----------+----------+---------
 empno    | numeric(4,0)          |           | not null |
 ename    | character varying(32) |           |          |
 job      | character varying(32) |           |          |
 mgr      | numeric(4,0)          |           |          |
 hiredate | date                  |           |          |
 sal      | numeric(10,2)         |           |          |
 comm     | numeric(10,2)         |           |          |
 deptno   | numeric(4,0)          |           |          |
Indexes:
    "emp_pk" PRIMARY KEY, btree (empno)
    "emp_deptno_ix" btree (deptno)
Foreign-key constraints:
    "emp_dept_fk" FOREIGN KEY (deptno) REFERENCES dept(deptno)
    "emp_manager_fk" FOREIGN KEY (mgr) REFERENCES emp(empno)
Referenced by:
    TABLE "emp" CONSTRAINT "emp_manager_fk" FOREIGN KEY (mgr) REFERENCES emp(empno)

db01=>

以下的 CSV 格式資料可以用 SQL Developer 從 Oracle 資料庫 export 出來。

dept.csv
10,"會計部","紐約"
20,"RESEARCH","DALLAS"
30,"SALES","CHICAGO"
40,"OPERATIONS","BOSTON"
70,"資訊部","台南 B4"
emp.csv
7839,"KING","PRESIDENT",,1981-11-17,5000,,10
7698,"BLAKE","MANAGER1",7839,1981-05-01,2850,101,30
7782,"陳瑞","MANAGER",7902,1981-06-09,2400,,10
7566,"陳賜珉","MANAGER",7839,1981-04-02,2975,,20
7788,"SCOTT","ANALYST",7566,1982-12-09,45300,,20
7902,"FORD","ANALYST",7566,1981-12-03,3000,,20
7369,"SMITH","CLERK",7902,1980-12-17,8001,,20
7499,"ALLEN","SALESMAN",7698,1981-02-20,1600,303,30
7608,"馬小九","ANALYST",7788,2010-06-28,1000,100,40
7654,"葉習堃","SALESMAN",7698,1981-09-28,1250,1400,30
7844,"하찮고","SALESMAN",7698,1981-09-08,1500,,30
7876,"ADAMS","CLERK",7788,1983-01-12,1100,,20
7900,"JAMES","CLERK",7698,1981-12-03,94998,,30
7934,"楊喆","CLERK",7902,1982-01-23,1500,,10
9006,"李逸君","ANALYST",7788,2001-05-07,66666,,70
7607,"バック","分析師",7788,2008-03-24,45000,100,70
7609,"蔡大一","分析師",7788,2010-06-28,60000,,70
9011,"文英蔡","總鋪師",7788,2018-08-28,77778,180,40
8907,"牸祢","ANALYST",7566,1982-12-09,9002,,10

將它 import 到 PostgreSQL。

import data
db01=> \copy dept from 'dept.csv' with csv;
COPY 5
db01=> \copy emp from 'emp.csv' with csv;
COPY 19
db01=> select * from emp;
 empno | ename  |    job    | mgr  |  hiredate  |   sal    |  comm   | deptno
-------+--------+-----------+------+------------+----------+---------+--------
  7839 | KING   | PRESIDENT |      | 1981-11-17 |  5000.00 |         |     10
  7698 | BLAKE  | MANAGER1  | 7839 | 1981-05-01 |  2850.00 |  101.00 |     30
  7782 | 陳瑞  | MANAGER   | 7902 | 1981-06-09 |  2400.00 |         |     10
  7566 | 陳賜珉 | MANAGER   | 7839 | 1981-04-02 |  2975.00 |         |     20
  7788 | SCOTT  | ANALYST   | 7566 | 1982-12-09 | 45300.00 |         |     20
  7902 | FORD   | ANALYST   | 7566 | 1981-12-03 |  3000.00 |         |     20
  7369 | SMITH  | CLERK     | 7902 | 1980-12-17 |  8001.00 |         |     20
  7499 | ALLEN  | SALESMAN  | 7698 | 1981-02-20 |  1600.00 |  303.00 |     30
  7608 | 馬小九 | ANALYST   | 7788 | 2010-06-28 |  1000.00 |  100.00 |     40
  7654 | 葉習堃 | SALESMAN  | 7698 | 1981-09-28 |  1250.00 | 1400.00 |     30
  7844 | 하찮고 | SALESMAN  | 7698 | 1981-09-08 |  1500.00 |         |     30
  7876 | ADAMS  | CLERK     | 7788 | 1983-01-12 |  1100.00 |         |     20
  7900 | JAMES  | CLERK     | 7698 | 1981-12-03 | 94998.00 |         |     30
  7934 | 楊喆   | CLERK     | 7902 | 1982-01-23 |  1500.00 |         |     10
  9006 | 李逸君 | ANALYST   | 7788 | 2001-05-07 | 66666.00 |         |     70
  7607 | バック | 分析師    | 7788 | 2008-03-24 | 45000.00 |  100.00 |     70
  7609 | 蔡大一 | 分析師    | 7788 | 2010-06-28 | 60000.00 |         |     70
  9011 | 文英蔡 | 總鋪師    | 7788 | 2018-08-28 | 77778.00 |  180.00 |     40
  8907 | 牸祢   | ANALYST   | 7566 | 1982-12-09 |  9002.00 |         |     10
(19 rows)

db01=>

PostgreSQL 提供 JSON 和許多支援的功能。JSON 已成為 Web 應用程序中最流行的數據交換格式。也支援 JSONB 資料型態。

row_to_json
db01=> select row_to_json(emp) as employee from emp;

                                                          employee                                                          
-----------------------------------------------------------------------------------------------------------------------------
 {"empno":7839,"ename":"KING","job":"PRESIDENT","mgr":null,"hiredate":"1981-11-17","sal":5000.00,"comm":null,"deptno":10}
 {"empno":7698,"ename":"BLAKE","job":"MANAGER1","mgr":7839,"hiredate":"1981-05-01","sal":2850.00,"comm":101.00,"deptno":30}
 {"empno":7782,"ename":"陳瑞","job":"MANAGER","mgr":7902,"hiredate":"1981-06-09","sal":2400.00,"comm":null,"deptno":10}
 {"empno":7566,"ename":"陳賜珉","job":"MANAGER","mgr":7839,"hiredate":"1981-04-02","sal":2975.00,"comm":null,"deptno":20}
 {"empno":7788,"ename":"SCOTT","job":"ANALYST","mgr":7566,"hiredate":"1982-12-09","sal":45300.00,"comm":null,"deptno":20}
 {"empno":7902,"ename":"FORD","job":"ANALYST","mgr":7566,"hiredate":"1981-12-03","sal":3000.00,"comm":null,"deptno":20}
 {"empno":7369,"ename":"SMITH","job":"CLERK","mgr":7902,"hiredate":"1980-12-17","sal":8001.00,"comm":null,"deptno":20}
 {"empno":7499,"ename":"ALLEN","job":"SALESMAN","mgr":7698,"hiredate":"1981-02-20","sal":1600.00,"comm":303.00,"deptno":30}
 {"empno":7608,"ename":"馬小九","job":"ANALYST","mgr":7788,"hiredate":"2010-06-28","sal":1000.00,"comm":100.00,"deptno":40}
 {"empno":7654,"ename":"葉習堃","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-28","sal":1250.00,"comm":1400.00,"deptno":30}
 {"empno":7844,"ename":"하찮고","job":"SALESMAN","mgr":7698,"hiredate":"1981-09-08","sal":1500.00,"comm":null,"deptno":30}
 {"empno":7876,"ename":"ADAMS","job":"CLERK","mgr":7788,"hiredate":"1983-01-12","sal":1100.00,"comm":null,"deptno":20}
 {"empno":7900,"ename":"JAMES","job":"CLERK","mgr":7698,"hiredate":"1981-12-03","sal":94998.00,"comm":null,"deptno":30}
 {"empno":7934,"ename":"楊喆","job":"CLERK","mgr":7902,"hiredate":"1982-01-23","sal":1500.00,"comm":null,"deptno":10}
 {"empno":9006,"ename":"李逸君","job":"ANALYST","mgr":7788,"hiredate":"2001-05-07","sal":66666.00,"comm":null,"deptno":70}
 {"empno":7607,"ename":"バック","job":"分析師","mgr":7788,"hiredate":"2008-03-24","sal":45000.00,"comm":100.00,"deptno":70}
 {"empno":7609,"ename":"蔡大一","job":"分析師","mgr":7788,"hiredate":"2010-06-28","sal":60000.00,"comm":null,"deptno":70}
 {"empno":9011,"ename":"文英蔡","job":"總鋪師","mgr":7788,"hiredate":"2018-08-28","sal":77778.00,"comm":180.00,"deptno":40}
 {"empno":8907,"ename":"牸祢","job":"ANALYST","mgr":7566,"hiredate":"1982-12-09","sal":9002.00,"comm":null,"deptno":10}
(19 rows)

PostgreSQL 資料準備好了，回到 C#。

C# EFCore 2.0 and PostgreSQL Provider

開啟 VS Code 建立一個專案。

$ > dotnet new console --name PgEFCoreSample

$ > cd PgEFCoreSample

$ PgEFCoreSample> dotnet run
Hello World!

首先要安裝專案需要的 NuGet Packages，這裡只需要將 Oracle Provider 換成 PostgreSQL Provider。這裡因用的 PostgreSQL Provider Npgsql.EntityFrameworkCore.PostgreSQL 版本比較新，所以也將 Microsoft EntityFramework Core 的版本更新。

$ PgEFCoreSample> dotnet add package Npgsql.EntityFrameworkCore.PostgreSQL --version 3.1.4 

$ PgEFCoreSample> dotnet add package Microsoft.EntityFrameworkCore.Design --version 3.1.4

$ PgEFCoreSample> dotnet add package Microsoft.EntityFrameworkCore.Relational --version 3.1.4

$ PgEFCoreSample> dotnet add package Microsoft.Extensions.Configuration.Json --version 3.1.4

現在專案目錄下的 PgEFCoreSample.csproj 檔案應該如下:

PgEFCoreSample.csproj
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
<Project Sdk="Microsoft.NET.Sdk">

  <PropertyGroup>
    <OutputType>Exe</OutputType>
    <TargetFramework>netcoreapp3.1</TargetFramework>
  </PropertyGroup>

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="3.1.4">
      <IncludeAssets>runtime; build; native; contentfiles; analyzers; buildtransitive</IncludeAssets>
      <PrivateAssets>all</PrivateAssets>
    </PackageReference>
    <PackageReference Include="Microsoft.EntityFrameworkCore.Relational" Version="3.1.4" />
    <PackageReference Include="Microsoft.Extensions.Configuration.Json" Version="3.1.4" />
    <PackageReference Include="Npgsql.EntityFrameworkCore.PostgreSQL" Version="3.1.4" />
  </ItemGroup>

</Project>

這裡也將 PostgreSQL 資料庫的連結資料放在 appsettings.json

appsettings.json
1
2
3
4
5
6
7
{
  "Data": {
    "DefaultConnection": {
      "ConnectionString": "Host=10.11.xx.xxx;Database=db01;Username=xxx;Password=xxxxxxxxxx" 
    }
  }
}

PostgreSQL 資料庫位在我的 PC 虛擬機 Ubuntu Linux 上，只開放在上班時間。

反向工程 (Reverse Engineering)

直接使用反向工程產生資料模型。

$ PgEFCoreSample> dotnet ef dbcontext scaffold "Host=10.11.xx.xxx;Database=db01;Username=xxxx;Password=xxxxxxxx" Npgsql.EntityFrameworkCore.PostgreSQL --table emp --table dept -o Models -f

它會在專案目錄下產生子目錄 Models 與 Dept.cs、Emp.cs 與 db01Context.cs。 這裡只要修改 db01Context.cs 的資料庫連結資料，讀取 appsettings.json 的設定。其他都不用動。

Models/db01Context.cs
using System;
using System.IO;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.Configuration.Json;
.....
        optionsBuilder.UseNpgsql(GetConnectionString());
.....
    private static string GetConnectionString()
    {
      var configurationBuilder = new ConfigurationBuilder()
        .SetBasePath(Directory.GetCurrentDirectory())
        .AddJsonFile("appsettings.json");
      IConfiguration config = configurationBuilder.Build();
      string connectionString = config["Data:DefaultConnection:ConnectionString"];
      return connectionString;
    }
.....

資料讀取

program.cs
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
using System;
using System.Linq;
using PgEFCoreSample.Models;

namespace PgEFCoreSample
{
  class Program
  {
    static void Main(string[] args)
    {
      Console.WriteLine("Hello Tainan! 台南! PostgreSQL!");

      using (var db = new db01Context())
      {
        Console.WriteLine("===== Departments ==========");
        foreach (var dept in db.Dept)
        {
          Console.WriteLine($"{dept.Deptno} {dept.Dname} {dept.Loc}");
        }

        Console.WriteLine("===== Employees ==========");
        foreach (var emp in db.Emp)
        {
          Console.WriteLine($"{emp.Empno} {emp.Ename} {emp.Job} {emp.Mgr} {emp.Hiredate?.ToString("yyyy-MM-ddTHH:mm:ss")} {emp.Sal} {emp.Comm} {emp.Deptno}");
        }
      }
    }
  }
}
$ PgEFCoreSample> dotnet run
Hello Tainan! 台南! PostgreSQL!
===== Departments ==========
10 會計部 紐約
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
70 資訊部 台南 B4
===== Employees ==========
7839 KING PRESIDENT  1981-11-17T00:00:00 5000.00  10
.....

這與使用 Oracle Provider 的程式碼，只有 db01Context 名稱不一樣，其他都一樣。你可以複製新增、修改、刪除與其它程式碼來試試看。更換資料庫，只要抽換掉資料庫的 Provider。

PostgreSQL Schema 範例

這裡提供一個比較實用的 PostgreSQL Schema 範例，這原來是 Oracle 的 Schema。其實 DDL 都一樣，需要改的是資料型態。

這裡是 DDL 範例與範例資料 demo_ot_data.sql。

demo_ot_schema.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
CREATE TABLE regions
  (
    region_id   INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 5) PRIMARY KEY,
    region_name VARCHAR(50) NOT NULL
  );

CREATE TABLE countries
  (
    country_id    CHAR(2) PRIMARY KEY,
    country_name  VARCHAR(40) NOT NULL,
     region_id    INTEGER,
    CONSTRAINT fk_countries_regions FOREIGN KEY (region_id)
      REFERENCES regions(region_id)
      ON DELETE CASCADE
  );

CREATE TABLE locations
  (
    location_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 24) PRIMARY KEY,
    address     VARCHAR(255) NOT NULL,
    postal_code VARCHAR(20),
    city        VARCHAR(50),
    state       VARCHAR(50),
    country_id  CHAR(2),    
    CONSTRAINT fk_locations_countries FOREIGN KEY (country_id)
      REFERENCES countries(country_id) 
      ON DELETE CASCADE
  );

CREATE TABLE warehouses
  (
    warehouse_id   INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 10) PRIMARY KEY,
    warehouse_name VARCHAR(255),
    location_id    INTEGER, 
    CONSTRAINT fk_warehouses_locations 
      FOREIGN KEY (location_id)
      REFERENCES locations(location_id) 
      ON DELETE CASCADE
  );

CREATE TABLE employees
  (
    employee_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 108) PRIMARY KEY,
    first_name  VARCHAR(255) NOT NULL,
    last_name   VARCHAR(255) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    phone       VARCHAR(50) NOT NULL ,
    hire_date   TIMESTAMP NOT NULL          ,
    manager_id  INTEGER ,
    job_title   VARCHAR(255) NOT NULL,
    CONSTRAINT fk_employees_manager 
        FOREIGN KEY (manager_id)
        REFERENCES employees(employee_id)
        ON DELETE CASCADE
  );

CREATE TABLE product_categories
  (
    category_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 6) PRIMARY KEY,
    category_name VARCHAR(255) NOT NULL
  );

CREATE TABLE products
  (
    product_id INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 289) PRIMARY KEY,
    product_name  VARCHAR(255) NOT NULL,
    description   VARCHAR(2000),
    standard_cost NUMERIC(9, 2),
    list_price    NUMERIC(9, 2),
    category_id   INTEGER NOT NULL ,
    CONSTRAINT fk_products_categories 
      FOREIGN KEY (category_id)
      REFERENCES product_categories(category_id) 
      ON DELETE CASCADE
  );

CREATE TABLE customers
  (
    customer_id  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 320) PRIMARY KEY,
    name         VARCHAR(255) NOT NULL,
    address      VARCHAR(255),
    website      VARCHAR(255),
    credit_limit NUMERIC(8, 2)
  );

CREATE TABLE contacts
  (
    contact_id  INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 320) PRIMARY KEY,
    first_name  VARCHAR(255) NOT NULL,
    last_name   VARCHAR(255) NOT NULL,
    email       VARCHAR(255) NOT NULL,
    phone       VARCHAR(20),
    customer_id INTEGER,
    CONSTRAINT fk_contacts_customers 
      FOREIGN KEY (customer_id)
      REFERENCES customers(customer_id) 
      ON DELETE CASCADE
  );

CREATE TABLE orders
  (
    order_id    INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 106) PRIMARY KEY,
    customer_id INTEGER NOT NULL,
    status      VARCHAR(20) NOT NULL,
    salesman_id INTEGER,
    order_date  TIMESTAMP NOT NULL,
    CONSTRAINT fk_orders_customers 
      FOREIGN KEY (customer_id)
      REFERENCES customers(customer_id)
      ON DELETE CASCADE,
    CONSTRAINT fk_orders_employees 
      FOREIGN KEY (salesman_id)
      REFERENCES employees(employee_id) 
      ON DELETE SET NULL
  );

CREATE TABLE order_items
  (
    order_id   INTEGER, 
    item_id    INTEGER,
    product_id INTEGER NOT NULL,
    quantity   NUMERIC(8, 2) NOT NULL,
    unit_price NUMERIC(8, 2) NOT NULL,
    CONSTRAINT pk_order_items 
      PRIMARY KEY (order_id, item_id),
    CONSTRAINT fk_order_items_products 
      FOREIGN KEY (product_id)
      REFERENCES products(product_id) 
      ON DELETE CASCADE,
    CONSTRAINT fk_order_items_orders 
      FOREIGN KEY (order_id)
      REFERENCES orders(order_id) 
      ON DELETE CASCADE
  );

CREATE TABLE inventories
  (
    product_id   INTEGER,
    warehouse_id INTEGER,
    quantity     NUMERIC(8, 0) NOT NULL,
    CONSTRAINT pk_inventories 
      PRIMARY KEY (product_id, warehouse_id),
    CONSTRAINT fk_inventories_products 
      FOREIGN KEY (product_id)
      REFERENCES products(product_id) 
      ON DELETE CASCADE,
    CONSTRAINT fk_inventories_warehouses 
      FOREIGN KEY (warehouse_id)
      REFERENCES warehouses(warehouse_id) 
      ON DELETE CASCADE
  );