Database Desing Problem

This is a discussion on Database Desing Problem within the A Brief History of Cprogramming.com forums, part of the Community Boards category; Ok i have a table by name assignment... I have used two keys to identigy a row uniquely.. Code: create ...

  1. #1
    Its not rocket science vasanth's Avatar
    Join Date
    Jan 2002
    Posts
    1,683

    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

  2. #2
    Crazy Fool Perspective's Avatar
    Join Date
    Jan 2003
    Location
    Canada
    Posts
    2,640
    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.

  3. #3
    Its not rocket science vasanth's Avatar
    Join Date
    Jan 2002
    Posts
    1,683
    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..

  4. #4
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,138
    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.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  5. #5
    Its not rocket science vasanth's Avatar
    Join Date
    Jan 2002
    Posts
    1,683
    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...

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. Replies: 10
    Last Post: 05-18-2006, 11:23 PM
  2. Bin packing problem....
    By 81N4RY_DR460N in forum C++ Programming
    Replies: 0
    Last Post: 08-01-2005, 05:20 AM
  3. Developing database management software
    By jdm in forum C++ Programming
    Replies: 4
    Last Post: 06-15-2004, 04:06 PM
  4. Making a Simple Database System
    By Speedy5 in forum C++ Programming
    Replies: 1
    Last Post: 03-14-2003, 09:17 PM
  5. File Database & Data Structure :: C++
    By kuphryn in forum C++ Programming
    Replies: 0
    Last Post: 02-24-2002, 10:47 AM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21