Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2009
    Posts
    56

    Unanswered: multiple foreign keys

    Hello,
    I have no experience with multiple foreign keys.
    In`table2` of the example
    Code:
    CREATE TABLE `table1` (
    		`serviceId` TINYINT(3) UNSIGNED NOT NULL
    	,	`serviceTypeId` TINYINT(3) UNSIGNED NOT NULL
    	,	`serviceTerm` VARCHAR(50) NOT NULL
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;
    
    CREATE TABLE `table2` (
    		`jobId` TINYINT(3) UNSIGNED NOT NULL
    	,	`serviceId` TINYINT(3) UNSIGNED NOT NULL
    	,	`jobTerm` VARCHAR(50) NOT NULL
    )
    COLLATE='utf8_general_ci'
    ENGINE=InnoDB;
    I need to create a foreign key constraint for column `table2`.`serviceId` using `table1`.`serviceId` which will only be valid for rows from `table1`.`serviceId` which have `table1`.`serviceTypeId` set to 2.

    Can anyone help me with the
    CONSTRAINT / INDEX syntax ?

    Thanks,
    Udo

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by vivoices View Post
    ... which will only be valid for rows from `table1`.`serviceId` which have `table1`.`serviceTypeId` set to 2.
    here's a reference -- Data Based : Distributed Keys and Disjoint Subtypes

    be aware that mysql does not support CHECK constraints
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jul 2009
    Posts
    56
    Thanks for the info.

    Without a CHECK constraint it seems to be possible to emulate a similar behavior with TRIGGER.

    I will leave implementing the restriction only in the (PHP) business logic for now.
    Will leave it for later to decide if to go with TRIGGERs or move to Oracle on a virtual private server.

    I do not know if Oracle will be needed at all performance wise.
    This will not develop into a multi-Terra-byte Database.

Posting Permissions

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