Probleme de contraintes [Résolu]

[kedare]

Hello tout le monde,
j'ai un petit problème , j'essaie de créer plusieurs tables via cette requête SQL pour un programme que j'ai fait :

Code :

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
-- Databases Schemas for Viper4irc, for POSTGRESQL8.X
-- Created by Kedare
CREATE TABLE public.channels
(
  id serial NOT NULL,
  name character(255) NOT NULL,
  reg_date timestamp WITH time zone,
  last_activity timestamp WITH time zone,
  flags character(24),
  CONSTRAINT channels_pk PRIMARY KEY (id, name)
);
 
CREATE TABLE public.users
(
  id serial NOT NULL,
  name character(255) NOT NULL,
  reg_date timestamp WITH time zone,
  last_activity timestamp WITH time zone,
  admin_level integer DEFAULT 0,
  CONSTRAINT users_pk PRIMARY KEY (id, name)
);
 
 
CREATE TABLE public.messages
(
  id serial NOT NULL,
  content text NOT NULL,
  post_date timestamp WITH time zone NOT NULL,
  user_id integer NOT NULL,
  channel_id integer NOT NULL,
  CONSTRAINT messages_pk PRIMARY KEY (id, content, post_date, user_id, channel_id),
  CONSTRAINT messages_fk_channels FOREIGN KEY (channel_id)
      REFERENCES channels (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT messages_fk_users FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
 
 
CREATE TABLE public.user_on_channel
(
  id integer NOT NULL DEFAULT NEXTVAL('user_on_channel_id_seq'::regclass),
  user_id integer NOT NULL,
  channel_id integer NOT NULL,
  level integer,
  flags character(24),
  CONSTRAINT user_on_channel_pk PRIMARY KEY (id, user_id, channel_id),
  CONSTRAINT user_on_channel_fk_channels FOREIGN KEY (channel_id)
      REFERENCES channels (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT user_on_channel_fk_users FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);

seulement , j'ai une erreur, je ne vois pas du tout d'ou ca peut venir, voila l'erreur :

Code :

1
2
ERROR: there IS no UNIQUE constraint matching given KEYS FOR referenced TABLE "channels"
État SQL :42830

d'ou vient ce problème ? comment faire pour le corriger ?
merci
EDIT: Bon , au temps pour moi, je m'etait mélangé entre les Index et les PK.. voila le code qui fonctionne

Code :

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
-- Databases Schemas for Viper4irc, for POSTGRESQL8.X
-- Created by Kedare
CREATE TABLE public.channels
(
  id serial UNIQUE NOT NULL,
  name character(255) UNIQUE NOT NULL,
  reg_date timestamp WITH time zone,
  last_activity timestamp WITH time zone,
  flags character(24),
  CONSTRAINT channels_pk PRIMARY KEY (id, name)
);
 
CREATE TABLE public.users
(
  id serial UNIQUE NOT NULL,
  name character(255) UNIQUE NOT NULL,
  reg_date timestamp WITH time zone,
  last_activity timestamp WITH time zone,
  admin_level integer DEFAULT 0,
  CONSTRAINT users_pk PRIMARY KEY (id, name)
);
 
 
CREATE TABLE public.messages
(
  id serial NOT NULL,
  content text NOT NULL,
  post_date timestamp WITH time zone NOT NULL,
  user_id integer NOT NULL,
  channel_id integer NOT NULL,
  CONSTRAINT messages_pk PRIMARY KEY (id),
  CONSTRAINT messages_fk_channels FOREIGN KEY (channel_id)
      REFERENCES channels (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT messages_fk_users FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
 
 
CREATE TABLE public.user_on_channel
(
  id serial NOT NULL,
  user_id integer NOT NULL,
  channel_id integer NOT NULL,
  level integer,
  flags character(24),
  CONSTRAINT user_on_channel_pk PRIMARY KEY (id),
  CONSTRAINT user_on_channel_fk_channels FOREIGN KEY (channel_id)
      REFERENCES channels (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT user_on_channel_fk_users FOREIGN KEY (user_id)
      REFERENCES users (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
);
 
CREATE INDEX channels_idx ON channels (id,name);
CREATE INDEX users_idx ON users (id,name);
CREATE INDEX messages_idx ON messages (id,content,user_id,channel_id);
CREATE INDEX user_on_channel_idx ON user_on_channel (id,user_id,channel_id);