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 :
seulement , j'ai une erreur, je ne vois pas du tout d'ou ca peut venir, voila l'erreur :
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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 );
d'ou vient ce problème ? comment faire pour le corriger ?
Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2 ERROR: there is no unique constraint matching given keys for referenced table "channels" État SQL :42830
merci
EDIT: Bon , au temps pour moi, je m'etait mélangé entre les Index et les PK.. voila le code qui fonctionne
Code : Sélectionner tout - Visualiser dans une fenêtre à part
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);
Partager