Radicore Forum
Fast Uncompromising Discussions. FUDforum will get your users talking.

Home » RADICORE development » Application Development » Compound foreign keys in linking tables (Can I create a LINK1 between two tables where one or both of the tables have compound primary keys?)
Compound foreign keys in linking tables [message #4930] Tue, 25 August 2015 21:24 Go to next message
rafs is currently offline  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 Go to previous messageGo to next message
AJM is currently offline  AJM
Messages: 2275
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.


Re: Compound foreign keys in linking tables [message #4936 is a reply to message #4934] Wed, 26 August 2015 13:23 Go to previous message
rafs is currently offline  rafs
Messages: 69
Registered: May 2015
Member
When I removed the dummy primary key--by setting the "Parent field" and "Calculated field" to empty, then it worked.
Thanks.
Previous Topic: Choose null on add3 popup
Next Topic: How to provide choices for two compound foreign keys in a form?
Goto Forum:
  


Current Time: Tue Nov 30 10:03:51 EST 2021

Total time taken to generate the page: 0.00936 seconds