Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2004
    Posts
    30

    Unanswered: Foreign key problem

    I have 2 tables:

    CREATE TABLE _TEMP1
    (
    FIELD1 INT NOT NULL,
    FIELD2 DATETIME NOT NULL
    );
    ALTER TABLE _TEMP1
    ADD PRIMARY KEY (FIELD1, FIELD2);

    and table _TEMP2

    CREATE TABLE _TEMP2
    (
    FIELD3 INT NOT NULL
    --FOREIGN KEY REFERENCES _TEMP1 (FIELD1) ON DELETE NO ACTION
    );

    what I am trying to do it to make put a foreign key to the first table, but there they primary key is by 2 fields, that's why when I unremark the foreign key I receive :

    There are no primary or candidate keys in the referenced table '_TEMP1' that match the referencing column list in the foreign key 'FK___TEMP2__FIELD1__02084FDA'.

  2. #2
    Join Date
    May 2004
    Posts
    4
    BOL:

    FOREIGN KEY...REFERENCES

    Is a constraint that provides referential integrity for the data in the column or columns. FOREIGN KEY constraints require that each value in the column exists in the corresponding referenced column(s) in the referenced table. FOREIGN KEY constraints can reference only columns that are PRIMARY KEY or UNIQUE constraints in the referenced table or columns referenced in a UNIQUE INDEX on the referenced table.

  3. #3
    Join Date
    Feb 2004
    Posts
    492
    So what you could do instead is create a _temp1 with a primary key on either one of the two fields. Then create a unique index on both field1 and field2. When creating the table _temp2 reference to the primary key of _temp1.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    a foreign key must specify all fields of the primary key
    Code:
    CREATE TABLE _TEMP2
    (
    FIELD3 INT NOT NULL
    FIELD4 DATETIME NOT NULL
    FOREIGN KEY ( FIELD3,FIELD4 ) 
    REFERENCES _TEMP1 ( FIELD1,FIELD2 ) ON DELETE NO ACTION
    )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •