1

In drupal,the content type table and my own table is used to do some operations. Im having two tables namely Table1:'content_field_mem_id' & Table2:'Ex' description of my content_field_mem_id is

+--------------------+------------------+------+-----+---------+-------+
| Field              | Type             | Null | Key | Default | Extra |
+--------------------+------------------+------+-----+---------+-------+
| vid                | int(10) unsigned | NO   | PRI | 0       |       |
| nid                | int(10) unsigned | NO   | MUL | 0       |       |
| field_mem_id_value | int(11)          | YES  |     | NULL    |       |
| sid                | int(30)          | YES  | MUL | NULL    |       |
+--------------------+------------------+------+-----+---------+-------+
4 rows in set (0.00 sec)

The member Id values are inside the field_mem_id_value field. Another Table Ex description is shown below : 

mysql> desc Ex;
+---------------+-------------+------+-----+---------+----------------+
| Field         | Type        | Null | Key | Default | Extra          |
+---------------+-------------+------+-----+---------+----------------+
| sid           | int(30)     | NO   | PRI | NULL    | auto_increment |
| mem_id        | varchar(30) | YES  |     | NULL    |                |
| nature_of_due | tinytext    | YES  |     | NULL    |                |
| amount        | int(30)     | YES  |     | NULL    |                |
| date          | date        | YES  |     | NULL    |                |
| paidby        | tinytext    | YES  |     | NULL    |                |
+---------------+-------------+------+-----+---------+----------------+
6 rows in set (0.00 sec)

The above table Ex doesnt contain any values.i.e)the Ex table has 0 rows. After posting operation by the admin Ex table will gets affected.

My aim is to show the view to the user using the Ex table.

The content_field_mem_id table consists 1000 numbers of member id which was imported by me and inserted into that content_field_mem_id table.

The relationship between this two table is one to many relationship i.e) Each member can pay any of the fees . But member fees is must so one member can have many transactions on the second table.

How to join the both tables and how to set the foreign key constraints?. I have set the foreign constraint for the mem_id ,it throws me an error not an syntax error.

I want to join the tables and the second table has to be taken for the view purpose and second table member id can be duplicated cos of many transactions.

Improve this question

1 Answer 1

Reset to default
1

The relationship appears to be between content_field_mem_id.field_mem_id_value and ex.mem_id However, since content_field_mem_id.field_mem_id_value is an int and ex.mem_id is a varchar, you will need to cast/convert one of the columns as part of your join.

It would be more reliable (although slower) to convert the int column to a varchar.

You didn't say which DB technology you are using, so I can't show the exact syntax for the convert (it is very different between MS SQL/Oracle/DB2/etc).

Improve this answer
6
  • MySQL is used here. Now another issue,The engine type of the two tables are same. But im not able to create the foreign key for mem_id in the Ex table . It throws the error as <strong> ERROR 1005 (HY000): Can't create table 'tnfpc1.#sql-3a2_27' (errno: 150)</strong> whether this prob is causing cos of the coverting prob which was pointed out by you??Give the solutions for this also
    – Sridhar Ramakrishnan
    Mar 22, 2013 at 13:23
  • Its not necessary for the integer type in the first table. I would like to change it as varchar. provide me the alter table syntax for doing that conversion.
    – Sridhar Ramakrishnan
    Mar 22, 2013 at 13:27
  • i ran the query for the conversion of data type in the Ex table as alter table modify columnname int(30).But again i couldnt able to make the foreign key on this table. This time error goes as ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (tnfpc1.#sql-3a2_27, CONSTRAINT #sql-3a2_27_ibfk_1 FOREIGN KEY (mem_id) REFERENCES content_field_mem_id (field_mem_id_value))
    – Sridhar Ramakrishnan
    Mar 22, 2013 at 13:37
  • @Sridhar Ramakrishnan: You need to get rid of rows in detail table that violate constraint before adding FK (probably move them to another table). Also, you don't have unique constraint on master column (content_field_mem_id.field_mem_id_value) which may and almost for sure will cause many problems in the future (even though mysql lets you create such constraint)...
    – a1ex07
    Mar 22, 2013 at 16:14
  • Since table [ex] is already empty, it might be simpler to change the column type of mem_id column to be int(11). You could drop/re-create table [ex]. Then the FK should be pretty easy. If you get rows that won't insert, you might want to not-load them anyway, since they won't show up in your reports/queries and are basically orphaned records anyway.
    – TimG
    Mar 22, 2013 at 18:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.