• 締切済み
  • 困ってます

複数テーブルで ID の一意性を保つ

Debian GNU/Linux 3.1 で psql 8.1.2 を使用しています。 下記の要領でテーブルを作成し、2つのテーブルでIDが重ならないようにしようと意図しました。 CREATE TABLE t1 ( t1_id int2 UNIQUE NOT NULL, t1_name text NOT NULL ); NOTICE: CREATE TABLE / UNIQUE will create implicit index "t1_t1_id_key" for table "t1" CREATE TABLE CREATE TABLE t2 ( t2_id int2 UNIQUE NOT NULL, t2_name text NOT NULL ); NOTICE: CREATE TABLE / UNIQUE will create implicit index "t2_t2_id_key" for table "t2" CREATE TABLE CREATE FUNCTION unique_t12_id(int2) RETURNS BOOLEAN AS 'SELECT NOT ( EXISTS(SELECT * FROM t1 WHERE t1_id = $1) AND EXISTS(SELECT * FROM t2 WHERE t2_id = $1) );' LANGUAGE SQL; CREATE FUNCTION ALTER TABLE t1 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t1_id)); ALTER TABLE ALTER TABLE t2 ADD CONSTRAINT con_unique_t12 CHECK (unique_t12_id(t2_id)); ALTER TABLE ところが実際は上手くいきませんでした。 INSERT INTO t1 VALUES(1, 'test1'); INSERT 0 1 INSERT INTO t2 VALUES(2, 'test2'); INSERT 0 1 INSERT INTO t2 VALUES(2, 'test3'); ERROR: duplicate key violates unique constraint "t2_t2_id_key" # ここまでは期待通りの挙動です INSERT INTO t2 VALUES(1, 'test4'); INSERT 0 1 ここで制約が働いて欲しかったのですが、素通りでINSERTされています。 下記のように、操作後の関数の戻り値は「偽」なのでこの操作は制約に引っかかると思うのですが 何故意図したように動かないのでしょうか。 SELECT *,unique_t12_id(t2_id) FROM t2; t2_id | t2_name | unique_t12_id -------+---------+--------------- 2 | test2 | t 1 | test4 | f (2 rows) スペースが詰まって読みづらい箇所もありますが、よろしくお願いします。

共感・応援の気持ちを伝えよう!

  • 回答数3
  • 閲覧数936
  • ありがとう数12

みんなの回答

  • 回答No.3

直接の回答ではありませんが、2つのテーブルで重ならないIDが必要なら、ID発行用に一つの SEQUENCE を作っておいて、どちらのテーブルもIDの値はここから取得すればよいのでは?

共感・感謝の気持ちを伝えよう!

質問者からのお礼

回答ありがとうございます。 2つのテーブルの関係を説明しておりませんでした。 t2 は t1 から抜き取ったレコードから作成されるテーブルで、 t1 にレコードが残ったまま t2 に挿入される…といったことが起こらないようにするのが 制約を設ける意図でした。 従って t1 のIDをSEQUENCEから作成するのは良いのですが、 t2 のIDには t1 のIDで使用されていた値がそのまま入りますので SEQUENCEを使用するわけには行きません。 …もっとも、SEQUENCEを共有してIDを生成するケースであっても 各テーブルにわたって一意性を保つ制約は、それとは別に設けておきたいような気もします。

関連するQ&A

  • Using temporary; Using filesort を回避したい

    はじめまして。よろしくお願いします。 下記の場合、Using temporary; Using filesort を回避するには どうしたらよいでしょうか。 次の 2 つのテーブルがあります。 CREATE TABLE `test1` ( `f1` int(11) NOT NULL, `f2` int(11) NOT NULL, PRIMARY KEY (`f1`,`f2`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test1` VALUES ('1', '2'); INSERT INTO `test1` VALUES ('1', '3'); INSERT INTO `test1` VALUES ('1', '4'); INSERT INTO `test1` VALUES ('2', '1'); INSERT INTO `test1` VALUES ('2', '3'); CREATE TABLE `test2` ( `f1` int(11) NOT NULL, `f2` varchar(20) default NULL, `f3` varchar(20) default NULL, PRIMARY KEY (`f1`), KEY `ix_f2_f3` (`f2`,`f3`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test2` VALUES ('1', 'A', 'a'); INSERT INTO `test2` VALUES ('2', 'B', 'b'); INSERT INTO `test2` VALUES ('3', 'C', 'c'); INSERT INTO `test2` VALUES ('4', 'D', 'd'); 次の SELECT を実行すると Using temporary; Using filesort が 現れてしまいます。 explain select test2.f2, test2.f3 from test1, test2 where test1.f1 = 1 and test1.f2 = test2.f1 order by test2.f2, test2.f3; order by ... をなくすと問題ないのですが、ソートは必要です。 複数のキーに対してORDER BYを実行する場合 インデックスを使用できないとあるので ix_f2_f3 は使用されず Using filesort となるのはわかるのですが、 Using temporary となる理由と回避方法がわかりません。 ご教示よろしくおねがいいたします。

  • SQL文について

    SQLite バージョン3.7.10 を使用しています。 次のようなテーブル構造で、 PRAGMA foreign_keys = ON; CREATE TABLE t1 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1'); INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2'); CREATE TABLE t2 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t2 (key, name) VALUES ('t2_key1', 't2_name1'); INSERT INTO t2 (key, name) VALUES ('t2_key2', 't2_name2'); CREATE TABLE t3 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t3 (key, name) VALUES ('t3_key1', 't3_name1'); INSERT INTO t3 (key, name) VALUES ('t3_key2', 't3_name2'); CREATE TABLE t4 ( year TEXT NOT NULL, t1_key TEXT NOT NULL, t3_key TEXT NOT NULL, percent INTEGER NOT NULL, UNIQUE(year, t1_key, t3_key), FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE, FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE ); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2011', 't1_key1', 't3_key1', 10); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key2', 50); INSERT INTO t4 (year, t1_key, t3_key, percent) VALUES ('2012', 't1_key2', 't3_key1', 100); CREATE TABLE t5 ( no INTEGER PRIMARY KEY AUTOINCREMENT, date TEXT NOT NULL, t1_key TEXT NOT NULL, t2_key TEXT NOT NULL, t3_key TEXT NOT NULL, amount INTEGER NOT NULL, FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE, FOREIGN KEY(t2_key) REFERENCES t2(key) ON DELETE CASCADE, FOREIGN KEY(t3_key) REFERENCES t3(key) ON DELETE CASCADE ); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-01-01', 't1_key1', 't2_key1', 't3_key1', 3000); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2011-02-02', 't1_key2', 't2_key1', 't3_key1', 2000); INSERT INTO t5 (date, t1_key, t2_key, t3_key, amount) VALUES ('2012-01-01', 't1_key2', 't2_key1', 't3_key1', 5000); 例えばテーブル「t5」から、 dateカラム=’2011’ t1_keyカラム= (テーブル「t1」のt1_nameカラム=’t1_name1’のt1_key1) t2_keyカラム= (テーブル「t2」のt2_nameカラム=’t2_name1’のt2_key1) という条件の含まれてる全ての対応する行に、 テーブル「t4」のpercentカラムとテーブル「t5」のamountカラムを 掛け合わせた数値の合計を出力する。 ただしテーブル「t4」に対応するt1_keyとt3_keyがなければ、 percentを「0」としてamountと掛ける。 例えばこの条件だと '2011-01-01'と'2011-02-02'の2つの行のamountの合計を求めることになりますが、 '2011-01-01'の方は、テーブル「t4」にpercent「10」があるので 3000×(10/100)=300 '2011-02-02'の方は、テーブル「t4」にpercentがない(対応する行がない)ので 2000×(0/100)=0 合計 300+0=300 という結果が得たいです。 これはどのようなSQL文になるのでしょうか?

  • 同じ構成の2つのテーブルの更新について

    はじめまして、下のような2つの同じ構成のテーブルがあった場合に create table test1 ( key1 varchar(8) not null, key2 varchar(4) not null, key3 varchar(6) not null, key4 varchar(11) not null, suryo int(9), kingaku int(11), constraint test1_key primary key (key1,key2,key3,key4) ); create table test2 ( key1 varchar(8) not null, key2 varchar(4) not null, key3 varchar(6) not null, key4 varchar(11) not null, suryo int(9), kingaku int(11), constraint test2_key primary key (key1,key2,key3,key4) ); 10万件のtest1テーブルに 5件のtest2テーブルの内容をキー集計した結果を test1に反映するにはどうのようなSQL文を書けば、効率的なのでしょうか? 片方のテーブルはデータ量が多く、もう片方は数件の更新処理となります。 宜しくお願いいたします。 ・処理前 (test1テーブル) key1,key2,key3,key4,suryo,kingaku "20070521","1111","111111","11111111111",10,1000 "20070521","2222","222222","22222222222",5,5000 "20070521","3333","333333","33333333333",1, 100 (test2テーブル) key1,key2,key3,key4,suryo,kingaku "20070521","2222","222222","22222222222",10,30000 ↓ ・処理後 (test1テーブル) key1,key2,key3,key4,suryo,kingaku "20070521","1111","111111","11111111111",10,1000 "20070521","2222","222222","22222222222",15,35000 "20070521","3333","333333","33333333333",1, 100

    • ベストアンサー
    • MySQL
  • 回答No.2

PL/PGSQLだとうまくいきますね CREATE or replace FUNCTION UNIQUE_t12_id(int) RETURNS BOOLEAN AS' DECLARE I INT; BEGIN SELECT INTO I T1_ID FROM t1 WHERE t1_id = $1; IF FOUND THEN RETURN FALSE; END IF; SELECT INTO I T2_ID FROM t2 WHERE t2_id = $1; IF FOUND THEN RETURN FALSE; END IF; RETURN TRUE; END;' LANGUAGE PLPGSQL VOLATILE;

共感・感謝の気持ちを伝えよう!

質問者からのお礼

またまたありがとうございます。 ご回答の関数では、やはり $1 がどちらか片方のテーブルにあった場合でも「f」が返ります。 が、制約としてはそれでいいのですね。ドキュメントのCHECKに関する記事には納得いきませんが(苦笑)。 私がやりたかったこと自体は nora1962 様の先の回答でも 今回の回答でも実現できますので、 ありがたく採用させていただきます。 なお、SQL関数で CREATE FUNCTION unique_t12_id(int2) RETURNS BOOLEAN AS 'SELECT NOT (EXISTS(SELECT * FROM t1 WHERE t1_id = $1) OR EXISTS(SELECT * FROM t2 WHERE t2_id = $1));' LANGUAGE SQL; と表現しても良いようです。

  • 回答No.1

FUNCTION で複数のテーブルを参照しているとダメみたいですね。 CREATE or replace FUNCTION CHK_t1_id(int) RETURNS BOOLEAN AS 'SELECT NOT EXISTS(SELECT * FROM t1 WHERE t1_id = $1);' LANGUAGE SQL VOLATILE; CREATE or replace FUNCTION CHK_t2_id(int) RETURNS BOOLEAN AS 'SELECT NOT EXISTS(SELECT * FROM t2 WHERE t2_id = $1);' LANGUAGE SQL VOLATILE; ALTER TABLE t1 ADD CONSTRAINT con_unique_t12 CHECK ( CHK_T2_ID(T1_ID) AND CHK_T1_ID(T1_ID) ); ALTER TABLE t2 ADD CONSTRAINT con_unique_t12 CHECK ( CHK_T1_ID(T2_ID) AND CHK_T2_ID(T2_ID) ); だとうまくいくみたいです。

共感・感謝の気持ちを伝えよう!

質問者からのお礼

回答ありがとうございます。 回答の制約ですと 「この数字はt1にもt2にも存在してはならない」 という条件になりますね。 私が意図していたのは 「この数字はt1かt2、片方の存在は許可するが両方に存在してはならない」 という条件なんです。 実際に回答の制約で試したところ、やはり SELECT bool(chk_t2_id(t1_id) AND chk_t1_id(t1_id)) FROM t1; の結果は挿入できたすべての行で「f」でした。 postgresql 8.1.2 文書の「CREATE TABLE」に関するページ ( http://www.postgresql.jp/document/pg812doc/html/sql-createtable.html ) の中ほどにCHECK句について書かれた部分があり、そこでは ------------------------------------------- 行の挿入、更新操作の結果、式がFALSEとなる場合は、エラー例外が生成され、 挿入や更新によるデータベースの変更は行われません。 ------------------------------------------- とあったので、操作後の状態で制約条件を審査し、結果「f」なら差し戻すのかと思っていましたが 実際はどうやら挿入や更新される行の値を用いて審査するが、 その際に関数を使ったりする場合は操作前のデータベースが参照されるようですね。 CHECK句でサブクエリが使用できないのもそのあたりに関連しているのでしょうか…。

関連するQ&A

  • 閉包テーブルのノードの移行について

    このジャンルでお願いします。 次のようなテーブルで CREATE TABLE IF NOT EXISTS item ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO item (name) VALUES ('item1'); INSERT INTO item (name) VALUES ('item2'); INSERT INTO item (name) VALUES ('item3'); INSERT INTO item (name) VALUES ('item4'); INSERT INTO item (name) VALUES ('item5'); INSERT INTO item (name) VALUES ('item6'); CREATE TABLE IF NOT EXISTS treepath ( ancestor_id int(11) NOT NULL, descendant_id int(11) NOT NULL, FOREIGN KEY (ancestor_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (descendant_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(ancestor_id, descendant_id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 1); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 2); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 3); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 4); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 5); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (1, 6); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 2); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 4); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 5); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (2, 6); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (3, 3); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (4, 4); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (4, 6); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (5, 5); INSERT INTO treepath (ancestor_id, descendant_id) VALUES (6, 6); item1┬item2    │ ├item4    │ │  └item6    │ └item5    │    └item3 を item1┬item2    │ │      │ └item5    │    └item3      └item4        └item6 のようにitem4以下のノードをitem3の下に移したいのですが、 その場合のtreepathテーブルの更新はどのようなSQL文にすれば良いのでしょうか?

    • ベストアンサー
    • MySQL
  • テーブル自体のバックアップ

    教えてください。 今オラクルのDBにTEST1テーブルが存在するとします。 ------TEST1--------------------- id varchar(5) Primary Key name varchar(20) old int -------------------------------- 今このテーブルとまったく同じデータを持ち、同じ構成のテーブルTEST2を 作成したいと考えています。 (1)create table TEST2( id varchar(5) primary key, name varchar(20), old int ); (2)insert into TEST2( id, name, old) values (select id, name, old from TEST1); としてTEST2テーブルを作成して、TEST1のデータを格納していますが、 他の方法はないでしょうか? たとえばこんなコマンドはありませんが、 COPY TEST1 TEST2 とか、 オラクルマネージャみたいなもので、 コピペして、名前をTEST2にする とかなんでもいいです。 TEST1のバックアップをとりたいのですが・・・・ すみません、わかりづらい説明で。 宜しくお願い致しますm(_ _)m

  • 指定した条件の組み合わせのSQL文

    SQLite バージョン3.7.10 を使用しています。 以下のようなテーブル構造で、 「t1」テーブルの全ての行と 「t6」テーブルのdateカラムの年の部分を グループ化して全ての組み合わせの 「t6」テーブルのamount の合計を出力したいのですが、 どのようなSQL文を書けば良いのでしょうか? 例えばこのデータ内容なら、以下のような結果が得たいです。 +------+-------+-------+ | year | t1_name | 合計 | +------+-------+-------+ | 2011 |t1_name1|  5000| +------+-------+------+ | 2012 |t1_name1| 10000| +------+-------+------+ | 2012 |t1_name2| 45000| +------+-------+------+ PRAGMA foreign_keys = ON; DROP TABLE IF EXISTS t6; DROP TABLE IF EXISTS t1; CREATE TABLE t1 ( key TEXT PRIMARY KEY NOT NULL, name TEXT NOT NULL UNIQUE ); INSERT INTO t1 (key, name) VALUES ('t1_key1', 't1_name1'); INSERT INTO t1 (key, name) VALUES ('t1_key2', 't1_name2'); CREATE TABLE t6 ( no INTEGER PRIMARY KEY AUTOINCREMENT, t1_key TEXT NOT NULL, date TEXT NOT NULL, amount INTEGER NOT NULL, FOREIGN KEY(t1_key) REFERENCES t1(key) ON DELETE CASCADE ); CREATE INDEX date on t6(date); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key1', '2011-01-01', 5000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key1', '2012-01-01', 10000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key2', '2012-01-01', 15000); INSERT INTO t6 (t1_key, date, amount) VALUES ('t1_key2', '2012-02-01', 30000); http://ideone.com/I1mMba

  • 結合?の仕方がわからず困っています。

    こんにちは、ほぼ初心者ですがよろしくお願いします。 sqlite2 で以下のような倉庫テーブルと在庫数テーブルがあったとして、 手順5で、ある品物(item_id = 1)の倉庫ごとの在庫数を取得したいと思っています。 倉庫に結合する在庫データがなくても、 倉庫に対する行を取得できる SQL 文を教えていただけませんでしょうか? ※そもそもテーブルの作り方がおかしいでしょうか? その辺も含めてアドバイスいただけるとありがたいです。 -- 以下sqlite です .mode column .header ON -- 手順1 倉庫テーブル CREATE TABLE stock_sites ( id INTEGER NOT NULL PRIMARY KEY , name TEXT NOT NULL ); -- 手順2 在庫テーブル CREATE TABLE stocks ( id INTEGER NOT NULL PRIMARY KEY , stock_site_id INTEGER NOT NULL, item_id INTEGER NOT NULL, stock INTEGER NOT NULL, UNIQUE(stock_site_id, item_id) ); -- 手順3、倉庫データ INSERT INTO stock_sites(name) VALUES('site_A'); INSERT INTO stock_sites(name) VALUES('site_B'); INSERT INTO stock_sites(name) VALUES('site_C'); -- 手順4、在庫データ INSERT INTO stocks(stock_site_id, item_id, stock) VALUES(1,1,10); INSERT INTO stocks(stock_site_id, item_id, stock) VALUES(2,2,20); -- 手順5 ある品の倉庫ごとの在庫数を取得したい SELECT t1.name AS site_name, t2.* FROM stock_sites AS t1 LEFT JOIN stocks AS t2 ON t1.id = t2.stock_site_id WHERE t2.item_id = 1 ORDER BY t1.id; -- 結果 -- site_name  t2.id   t2.stock_site_id t2.item_id t2.stock -- ---------- ---------- ---------------- ---------- ---------- -- site_A   1      1         1     10 -- 欲しい結果 -- site_name  t2.id   t2.stock_site_id t2.item_id t2.stock -- ---------- ---------- ---------------- ---------- ---------- -- site_A   1      1         1     10 -- site_B -- site_C

  • 階層構造のテーブルの行の取得について

    このジャンルでお願いします。 次のようなテーブルで CREATE TABLE IF NOT EXISTS user ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user (name) VALUES ('user1'); INSERT INTO user (name) VALUES ('user2'); INSERT INTO user (name) VALUES ('user3'); CREATE TABLE IF NOT EXISTS item ( id int(11) NOT NULL AUTO_INCREMENT, parent_id INT, name varchar(32) NOT NULL, FOREIGN KEY (parent_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO item (parent_id, name) VALUES (null, 'item1'); INSERT INTO item (parent_id, name) VALUES (1, 'item2'); INSERT INTO item (parent_id, name) VALUES (2, 'item3'); INSERT INTO item (parent_id, name) VALUES (3, 'item4'); INSERT INTO item (parent_id, name) VALUES (2, 'item5'); INSERT INTO item (parent_id, name) VALUES (null, 'item6'); INSERT INTO item (parent_id, name) VALUES (6, 'item7'); INSERT INTO item (parent_id, name) VALUES (null, 'item8'); CREATE TABLE IF NOT EXISTS user_item ( id int(11) NOT NULL AUTO_INCREMENT, user_id INT, item_id INT, type varchar(16) NOT NULL, PRIMARY KEY(id), FOREIGN KEY (user_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (item_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow'); INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny'); INSERT INTO user_item (user_id, item_id, type) VALUES (1, 7, 'allow'); user_itemのuser_idが1のitemの行を取得したいのですが、 その条件として ・user_itemテーブルのtypeが'allow'のitem_id以下のitemの行 ・ただしtypeが'deny'のitem_id以下の行は除く この例だと、user_itemの INSERT INTO user_item (user_id, item_id, type) VALUES (1, 1, 'allow'); の行によってitemテーブルの INSERT INTO item (parent_id, name) VALUES (null, 'item1'); INSERT INTO item (parent_id, name) VALUES (1, 'item2'); INSERT INTO item (parent_id, name) VALUES (2, 'item3'); INSERT INTO item (parent_id, name) VALUES (3, 'item4'); INSERT INTO item (parent_id, name) VALUES (2, 'item5'); が取得候補になりますが、 INSERT INTO user_item (user_id, item_id, type) VALUES (1, 3, 'deny'); によって INSERT INTO item (parent_id, name) VALUES (2, 'item3'); INSERT INTO item (parent_id, name) VALUES (3, 'item4'); の行は除かれ、 INSERT INTO item (parent_id, name) VALUES (null, 'item1'); INSERT INTO item (parent_id, name) VALUES (1, 'item2'); INSERT INTO item (parent_id, name) VALUES (2, 'item5'); の行が取得されます。 つまりitemテーブルにおいて上の階層の直近の(user_itemと結合して取得した)typeが 'allow'である場合のみ取得したいのです。 INSERT INTO item (parent_id, name) VALUES (null, 'item1');←取得 INSERT INTO item (parent_id, name) VALUES (1, 'item2');←取得 INSERT INTO item (parent_id, name) VALUES (2, 'item3'); INSERT INTO item (parent_id, name) VALUES (3, 'item4'); INSERT INTO item (parent_id, name) VALUES (2, 'item5');←取得 INSERT INTO item (parent_id, name) VALUES (null, 'item6'); INSERT INTO item (parent_id, name) VALUES (6, 'item7');←取得 INSERT INTO item (parent_id, name) VALUES (null, 'item8'); 複雑ではありますが、これはどのようなSQL文にすれば良いのでしょうか?

    • ベストアンサー
    • MySQL
  • 日付型項目のNULLについて(PostgreSQL)

    よろしくお願いします。 下記内容のtest_tableを作成して ---- CREATE TABLE "test_table" ( "id" varchar(10) NOT NULL, "fdate" date ); ---- 下記内容のデータを登録しました。 ---- insert into test_table(id,fdate) values ('abc',NULL); ---- 下記のsqlでデータを取得できませんでした。 どうしてでしょうか? select * from test_table where fdate = NULL;

  • 同時アクセス

    例えば bigCategory,midleCategoryテーブルがあり、(今回はmiddleCategoryがbigCategoryときーでつながっている) create table bigCategory ( num integer not null primary key, name varchar(20) not null, ); insert into bigCategory values(0,'category_1'); insert into bigCategory valuers(1,'category_2'); create table middleCategory ( num integer not null primary key, name varchar(20) not null, bigkey integer not null ); insert into middleCategory values(0,'middle_1',0); insert into middleCategory values(1,'middle_2',1); bigCategoryの変更・削除をするときに、 1)middleCategoryがselectされている → 変更不可 2)middleCategoryがselectされていない →変更・削除可能 3)また、middleCategoryはselectされてなくて、 bigCategoryテーブルの変更行が他からselectされていたら、変更・削除不可 といった状況を実現したいのですが、可能なのでしょうか? 候補として、おもうはGRANTかな、と思ったりはするのですが。 いろいろ考えはするのですが、解決策が思いつかず質問させてもらいました。 宜しくお願いします。

  • 最初に挿入された行だけ取得したい

    このジャンルでお願いします。 次のようなテーブルで CREATE TABLE IF NOT EXISTS user ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user (name) VALUES ('user1'); INSERT INTO user (name) VALUES ('user2'); INSERT INTO user (name) VALUES ('user3'); CREATE TABLE IF NOT EXISTS item ( id int(11) NOT NULL AUTO_INCREMENT, name varchar(32) NOT NULL, PRIMARY KEY(id) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO item (name) VALUES ('item1'); INSERT INTO item (name) VALUES ('item2'); INSERT INTO item (name) VALUES ('item3'); CREATE TABLE IF NOT EXISTS user_item ( id int(11) NOT NULL AUTO_INCREMENT, user_id INT, item_id INT, PRIMARY KEY(id), FOREIGN KEY (item_id) REFERENCES user(id) ON DELETE CASCADE ON UPDATE CASCADE, FOREIGN KEY (user_id) REFERENCES item(id) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO user_item (user_id, item_id) VALUES (1, 1); INSERT INTO user_item (user_id, item_id) VALUES (2, 1); INSERT INTO user_item (user_id, item_id) VALUES (3, 2); INSERT INTO user_item (user_id, item_id) VALUES (1, 2); INSERT INTO user_item (user_id, item_id) VALUES (1, 3); INSERT INTO user_item (user_id, item_id) VALUES (3, 3); user_itemテーブルでのselectで、 user_idが1で一番最初に挿入された行だけ を取得したいのですが、どのようなSQL文にすれば良いのでしょうか? この例なら INSERT INTO user_item (user_id, item_id) VALUES (1, 1);←取得 INSERT INTO user_item (user_id, item_id) VALUES (2, 1); INSERT INTO user_item (user_id, item_id) VALUES (3, 2); INSERT INTO user_item (user_id, item_id) VALUES (1, 2);←user_id=3が最初に挿入されているので取得しない INSERT INTO user_item (user_id, item_id) VALUES (1, 3);←取得 INSERT INTO user_item (user_id, item_id) VALUES (3, 3);

    • ベストアンサー
    • MySQL
  • uniqueキーをupdateで+1するとERROR 1062: Duplicate entry になる

    create table table1 ( a tinyint unsigned not null, b tinyint unsigned not null, unique index_table1 ( a, b ) ); insert into table1 values('1','1'); insert into table1 values('1','2'); update table1 set b = b+1 where a = '1'; とすると、 ERROR 1062: Duplicate entry '1-2' for key 1 とエラーになってしまいます。 どうやら unique の制約に引っかかっているみたいです。 結果としては 1,2 1,3 になればいいのですが、一個一個bが多い順番にSQLを発行するか、unique 制約を外すしかないんでしょうか? (面倒なので、一発でやりたいのです)

    • ベストアンサー
    • MySQL
  • MySQL SELECTの指定について

    まず、御覧頂きありがとうございます。 早速質問なのですが、 CREATE TABLE `hoge` ( `id` int(8) NOT NULL, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `hoge` (`id`, `name`) VALUES (1, 'hogeA'), (2, 'hogeB'), (3, 'hogeC'), (4, 'hogeD'); CREATE TABLE `test` ( `id` int(8) NOT NULL, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO `test` (`id`, `name`) VALUES (1, 'testA'), (4, 'testD'); という値が入ってるテーブルがあるとします。(長くなってすみません) で、 hoge.id と test.id が重複した場合 test.name を表示させたいです。 期待値としては id,name 1.testA 2,hogeB 3,hogeC 4,testD になります。 業務でJoinを使うようなテーブル構造にそもそもしないというのもあり、クリティカルなSQLコードに不慣れで御知恵を借りたく質問させて頂きました。 よろしくお願いします。

    • ベストアンサー
    • MySQL