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