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
i get the errorCode: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]%') );
could some one help me.. is it because the assignment_id alone does not uniquely identify a row in the assignment table or sometin elseCode: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.
thanx in advance