Database Desing Problem

Ok i have a table by name assignment...


I have used two keys to identigy a row uniquely..

Code:

create table assignment
(
assignment_id varchar(10) not null,
recorder_id varchar(10) not null,
assignment_date smalldatetime not null,


Constraint assignment_key primary key (assignment_id,recorder_id),
constraint assignment_rec_id foreign key (recorder_id) references recorder(recorder_id)on update no action on delete no action,
constraint assignment_id_check check (assignment_id like 'as%')


);

now when i try to create

Code:

create table site_species_record
(
assignment_id varchar(10) not null,
site_id varchar(10) not null,
species_ref_no varchar(10) not null,
species_rec_grid_ref varchar(15) not null,


Constraint site_species_record_key primary key (assignment_id,site_id,species_ref_no,species_rec_grid_ref),
constraint record_assignment_id  foreign key (assignment_id) references assignment(assignment_id)on update no action on delete no action,
constraint record_site_id foreign key (site_id) references site(site_id)on update no action on delete no action,
constraint record_species_ref_no foreign key (species_ref_no) references species(species_ref_no)on update no action on delete no action,
constraint species_rec_grid_ref_check check (species_rec_grid_ref like '[A-Z][A-Z]%')

);

i get the error

Code:

Server: Msg 1776, Level 16, State 1, Line 135
There are no primary or candidate keys in the referenced table 'assignment' that match the referencing column list in the foreign key 'record_assignment_id'.
Server: Msg 1750, Level 16, State 1, Line 135
Could not create constraint. See previous errors.

could some one help me.. is it because the assignment_id alone does not uniquely identify a row in the assignment table or sometin else

thanx in advance
vasanth

it is probably because the forgein key you are trying to reference does not exist. Most DBMS wont allow a non existant foreign key reference.

Originally posted by Perspective
it is probably because the forgein key you are trying to reference does not exist. Most DBMS wont allow a non existant foreign key reference.

well the key exists.. whch you can see was created during the creation of the table assignment..

Your primary key is on

Constraint assignment_key primary key (assignment_id,recorder_id)

Your foreign key must reference an existing primary key, but only references part of it:

constraint record_assignment_id foreign key (assignment_id) references assignment(assignment_id)

You need to make assignment_id the unique primary key, or put recorder_id in the table that needs the foreign key.

Originally posted by nvoigt
Your primary key is on

Constraint assignment_key primary key (assignment_id,recorder_id)

Your foreign key must reference an existing primary key, but only references part of it:

constraint record_assignment_id foreign key (assignment_id) references assignment(assignment_id)

You need to make assignment_id the unique primary key, or put recorder_id in the table that needs the foreign key.

thanks..... I got it.. now i have created a new table with assignment in and recorder id.. the assignment table only has one primary key now

Constraint assignment_key primary key (assignment_id)


Thanx...