68053


PostersSQLを勉強しています。MySQLでは下記のようにすると外部キー制約等リレーションがうまくいくのですが、PostersSQLの場合、少し構文が変わると思い変えてみてはいますがうまくいきません。

MySQL

[code]

    CREATE TABLE `users` (
      `username` varchar(80) NOT NULL,
      `password` varchar(80) NOT NULL,
      `enabled` tinyint(1) NOT NULL
    );
    
    CREATE TABLE `authorities` (
      `username` varchar(80) NOT NULL,
      `authority` varchar(80) NOT NULL
    );
    
    ALTER TABLE `users`
      ADD PRIMARY KEY (`username`);
    
    ALTER TABLE `authorities`
      ADD UNIQUE KEY `username` (`username`,`authority`);
    
    ALTER TABLE `authorities`
      ADD CONSTRAINT `authorities_ibfk_1` FOREIGN KEY (`username`) REFERENCES `users` (`username`);
    
[/code]

PostgreSQL

[code]

                                                                                                   
    CREATE TABLE users (  
    　username varchar(80) NOT NULL,  
    　password varchar(80) NOT NULL,
    　　　　　　　↓tinyintはないのでsmallintに変更  
    　enabled smallint(1) NOT NULL);
    
    CREATE TABLE authorities (  
    　username varchar(80) NOT NULL,  
    　authority varchar(80) NOT NULL);
    
                                                                                 
    ALTER TABLE users  
    　ADD PRIMARY KEY (username);
    
    ALTER TABLE authorities  
    　　　　↓UNIQUE KEY（PostgreSQLの場合はUNIQUE?
    　ADD UNIQUE username (username,authority);
    
    ALTER TABLE authorities  
    　ADD CONSTRAINT authorities_ibfk_1 FOREIGN KEY (username) REFERENCES users (username);
    
[/code]

上記のやり方によるエラー

[code]

    ERROR:  "("またはその近辺で構文エラー
    行 1: ...  password varchar(80) NOT NULL,  enabled smallint(1) NOT NU...
                                                               ^
    ERROR:  リレーション"authorities"はすでに存在します
    ERROR:  リレーション"users"は存在しません
    ERROR:  "username"またはその近辺で構文エラー
    行 1: ALTER TABLE authorities  ADD UNIQUE username (username,autho...
                                              ^
    ERROR:  リレーション"users"は存在しません
    
[/code]

・smallint(1)でエラーになっているので、smallintのみにする。  
・ALTER TABLE authorities ADD UNIQUE username (username,autho...部分の修正方法がわかりません。  
・その他修正が必要か？

ご教授いただけたら幸いです。


CC BY-SA 4.0
2020-06-25T10:40:14.297
67987
2020-06-28T10:28:32.063
40666
question
mysql postgresql
MySQL から PostgreSQLへの変換


> smallint(1)でエラーになっているので、smallintのみにする。

はい。  
もしくは桁数をユーザ指定精度にするならば`numeric`型をご検討ください。  
[数値データ型](https://www.postgresql.jp/document/8.2/html/datatype-numeric.html)

ちなみに`enabled numeric(1, 0) NOT NULL`のように1桁精度を指定した場合、小数点以下は丸められます。

[code]

    insert into users values('piyo', 'foo', 0.1); -- enabledが0に丸められる
    insert into users values('bar', 'buz', 10);    -- エラー
    
[/code]

> ALTER TABLE authorities ADD UNIQUE username

[CREATE INDEX](https://www.postgresql.jp/document/9.3/html/sql-
createindex.html)に書き換え可能です。

[code]

    CREATE UNIQUE INDEX username ON authorities (username,authority);
    
[/code]

[ALTER TABLE](https://www.postgresql.jp/document/9.4/html/sql-
altertable.html)でユニークキーを追加したことがないので自信はないのですが、リンク先の ADD
`table_constraint_using_index` の通り既存のインデックスを使用する構文ではないでしょうか。(下記の太字は筆者)

> この構文は、 **既存の一意性インデックス** に基づき、テーブルにPRIMARY KEYまたはUNIQUE制約を新たに追加します。

> その他修正が必要か？

SQL文に全角スペースが含まれているので半角スペースに変更した方が良いのではないでしょうか。

[SQL Fiddle](http://sqlfiddle.com/#!17/27d7a/1)でビルド可能なSQL：

[code]

    CREATE TABLE users (
      username varchar(80) NOT NULL,
      password varchar(80) NOT NULL,
    -- enabled smallint NOT NULL
      enabled numeric(1, 0) NOT NULL);
      
    CREATE TABLE authorities (
      username varchar(80) NOT NULL,
      authority varchar(80) NOT NULL);
      
    ALTER TABLE users  
      ADD PRIMARY KEY (username);
    
    CREATE UNIQUE INDEX username
      ON authorities (username,authority);
    
    ALTER TABLE authorities  
      ADD CONSTRAINT authorities_ibfk_1 FOREIGN KEY (username) REFERENCES users (username);
    
    insert into users values('hoge', 'fuga', 9);  -- 正常
    insert into users values('piyo', 'foo', 0.1); -- enabledが0に丸められる
    -- insert into users values('bar', 'buz', 10);    -- エラー
    
[/code]


CC BY-SA 4.0
2020-06-27T06:50:27.363
68053
2020-06-27T06:50:27.363
9820
67987
answer


PostgreSQL には boolean 型がありますが、MySQL には無いので 代わりに tinyint(1) が使われることがよくあります。

この enabled が true, false を保持するためのカラムなのであれば、boolean を使うのがいいと思います。


CC BY-SA 4.0
2020-06-28T10:28:32.063
68080
2020-06-28T10:28:32.063
3249
67987
answer