Mysql – can I combine foreign key with primary key in `id` column in 1 table?

foreign keyMySQLprimary-key

Here is the sample

CREATE TABLE apple(
 `appleId` int(10) NOT NULL,
 `apple_Name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`appleId`)
); 

ALTER TABLE fruit(
 `fruitID` int(10) NOT NULL AUTO_INCREAMENT = 1505,
 `fruitgroupname` varchar(100) DEFAULT NULL,
 `quantityfruit` int(5) DEFAULT NULL,
 `appleIDFK` int(10) NOT NULL,
 PRIMARY KEY (`fruitID`),
 UNIQUE KEY `apple` (`appleId`),
 CONSTRAINT apple_IDFK FOREIGN KEY (`appleIDFK`) REFERENCES apple(`appleId`)   
);

This is the output that what I want

fruitID     fruitgroupname   apple_name   quantityfruit
---------   --------------   ----------   -------------
1505-A001   Fruit Tree       Apple        100

I want to save fruitID like 1505-A001 (combining 2 Primary key in 1 column on HTML view)in fruit output. So, when I call it said like that. Combining 2 data on that output separate with - between fruitID and appleID?

Best Answer

It's not entirely clear what you're asking, and your SQL statements don't work, but assuming you mean something like this:

CREATE TABLE apple(
    `appleId` int(10) NOT NULL,  
    `apple_Name` varchar(100) DEFAULT NULL,   
    PRIMARY KEY (`appleId`) 
);

CREATE TABLE fruit(  
    `fruitID` int(10) NOT NULL AUTO_INCREMENT,  
    `fruitgroupname` varchar(100) DEFAULT NULL,  
    `quantityfruit` int(5) DEFAULT NULL,  
    `appleIDFK` int(10) NOT NULL,  
    PRIMARY KEY (`fruitID`),  
    CONSTRAINT apple_IDFK FOREIGN KEY (`appleIDFK`) REFERENCES apple(`appleId`)    
);

And data such as:

INSERT INTO apple (appleId, apple_Name) VALUES (1, 'Apple');
INSERT INTO fruit (fruitID, fruitGroupName, quantityfruit, appleIDFK) VALUES (1505, 'Fruit Tree', 100, 1);

Then you can get the sort of output you indicated with:

SELECT f.fruitID, f.fruitgroupname, a.apple_name, f.quantityfruit  
FROM fruit f 
    LEFT OUTER JOIN apple a ON f.appleIDFK = a.appleId;

Or, to get exactly the same output:

SELECT CONCAT(f.fruitID, '-A', LPAD(f.appleIDFK, 3, '0')) "fruitID",
   f.fruitgroupname, a.apple_name, f.quantityfruit
   FROM fruit f
      LEFT OUTER JOIN apple a ON f.appleIDFK = a.appleId;

Note that instead of LEFT OUTER JOIN you can use INNER JOIN which executes faster, but then you won't see any fruit records that aren't apples.