Compound foreign keys in linking tables [message #4930] |
Tue, 25 August 2015 21:24 |
rafs
Messages: 69 Registered: May 2015
|
Member |
|
|
Given the tables and relationships below, can you explain how to make the LINK1 relationship work from books to audiences?
I tried by defining a CALCULATED parent field for book to book_audiences with calculated field of CONCAT(book_type, ' ', book_name) AS book_type_name.
It gave me an SQL error.
I also tried with just using book_type as the parent field, unsuccessfully.
Is this possible? I don't see any examples of compound keys in linking tables in your demos.
CREATE TABLE book (
book_type char(3),
book_name character varying(100),
book_desc character varying(1000),
PRIMARY KEY (book_type, book_name)
);
CREATE TABLE audience (
aud_type char(3),
aud_name character varying(100),
aud_desc character varying(1000),
PRIMARY KEY (aud_type, aud_name)
);
CREATE TABLE book_audience (
book_type char(3),
book_name character varying(100),
aud_type char(3),
aud_name character varying(100),
PRIMARY KEY (book_type, book_name, aud_type, aud_name),
FOREIGN KEY (book_type, book_name) references book (book_type, book_name),
FOREIGN KEY (aud_type, aud_name) references audience (aud_type, aud_name)
);
Thanks.
[Updated on: Tue, 25 August 2015 21:24] Report message to a moderator
|
|
|
Re: Compound foreign keys in linking tables [message #4934 is a reply to message #4930] |
Wed, 26 August 2015 05:31 |
AJM
Messages: 2363 Registered: April 2006 Location: Surrey, UK
|
Senior Member |
|
|
Unlike some other frameworks, RADICORE does not expect primary keys to be limited to a single column, so it should not have any difficulty in handling compound primary keys as well as compound foreign keys.
The SQL query that the LINK1 pattern tries to build is shown at http://www.tonymarston.net/php-mysql/many-to-many.html#a5b such as
SELECT a.a_id, b.b_id, b.b_desc,
CASE WHEN x.b_id IS NULL THEN 'F' ELSE 'T' END AS selected
FROM a
CROSS JOIN b
LEFT JOIN x ON (x.a_id = a.a_id AND x.b_id = b.b_id)
WHERE (a.a_id = 'whatever')
What you need to do first is to build an equivalent query for your table structure, then try it out in your database client (phpMyadmin or HeidiSQL or whatever). When you get this working you should then see what query RADICORE builds by default. You will only need to take corrective action if the query is not correct. You certainly should not have to use CONCAT to construct a dummy primary key containing a single field.
Tony Marston
http://www.tonymarston.net
http://www.radicore.org
|
|
|
|