Foreign key on serial column 1

[TheBestOfMe]

Hi, I want to make have a foreign key on a integer column ta a serial column (the same thing as we can do in Access).

When I execute my file, the following error occurs:

ERROR: UNIQUE constraint matching given keys for referenced table "auberges" not found.

Here is an example:


create table Auberges
(
no_proprio varchar(8),
no_auberge serial,

PRIMARY KEY (no_proprio, no_auberge),
FOREIGN KEY (no_proprio) REFERENCES Proprietaires(no_proprio)
);

create table Auberges_Images
(
no_auberge int,
no_image serial,
lien_image varchar(100),

PRIMARY KEY (no_auberge, no_image),
FOREIGN KEY (no_auberge) REFERENCES Auberges(no_auberge)
);



Thanks.

 

[rosenk]

I'm believe that your problem is that no_auberge in the table Auberges is neither a foreign key, nor has a UNIQUE index. So there could be multiple rows with the same value for no_auberge. If you changed the def to:
[tt]create table Auberges
(
no_proprio varchar(8),
no_auberge serial UNIQUE,

PRIMARY KEY (no_proprio, no_auberge),
FOREIGN KEY (no_proprio) REFERENCES Proprietaires(no_proprio)
);[/tt]

I believe things would work.

 

[TheBestOfMe]

thanks, I'll try it.

 

[TheBestOfMe]

It has work. Strange because the serial column was a primary key and it is suppose to be unique by default.

 

[rosenk]

It's only part of the primary key on that table. You made the primary key the combination of no_auberge and no_proprio.

Glad that worked for you.