Hi Sri, By default Biomart use myisam for MySQL, if you use innodb, you need to uncheck the myisam checkbox when you add a new source, that will keep the relations you defined in the database.
When you do the materialize, there are some suffix in the column name to avoid the name conflict. Yong On 11-05-23 3:39 PM, "Sri Krishna Suresh Kumar" <[email protected]> wrote: > Hello, > > I am trying to set up a new biomart based system for our database. I > have few queries that I have in this process and request your help. > > When I successfully add a relational database, and view the schema > editor, the relations defined in the database does not get reflected, > and similarly when I deploy, the relations are not shown. > > So In order to understand this, I was working on a test database with 3 > tables. > > In this following set of table creation, the relations are incorrect in > Biomart. > CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB; > CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB; > CREATE TABLE product_order > ( > no INT NOT NULL AUTO_INCREMENT, > product_id1 INT NOT NULL, > customer_id INT NOT NULL, > PRIMARY KEY(no), > INDEX (product_id1), > FOREIGN KEY (product_id1) REFERENCES product(id), > INDEX (customer_id), > FOREIGN KEY (customer_id) REFERENCES customer(id) > ) ENGINE=INNODB; > > While When I use these following statements, I get the desired relation > CREATE TABLE product (id INT NOT NULL,PRIMARY KEY(id)) ENGINE=INNODB; > CREATE TABLE customer (id INT NOT NULL,PRIMARY KEY (id)) ENGINE=INNODB; > CREATE TABLE product_order > ( > no INT NOT NULL AUTO_INCREMENT, > product_id INT NOT NULL, > customer_id INT NOT NULL, > PRIMARY KEY(no), > INDEX (product_id), > FOREIGN KEY (product_id) REFERENCES product(id), > INDEX (customer_id), > FOREIGN KEY (customer_id) REFERENCES customer(id) > ) ENGINE=INNODB; > > The difference between the two is just the column name in the > product_order table. Here I notice that the reference is created > between a column with name ID (in parent), and table_ID ( in child) > and not otherwise. Could you confirm me if my observation is correct? > > Also, When I deploy or try to materialize, there are few values > suffixed to the column name. I was wondering how and where to change > this behaviour. > > Thanks and Regards, > Sri Krishna > > PS. If available, could you give me pointers to some tutorials on > setting up biomart for a new user. > _______________________________________________ > Users mailing list > [email protected] > https://lists.biomart.org/mailman/listinfo/users _______________________________________________ Users mailing list [email protected] https://lists.biomart.org/mailman/listinfo/users
