I need help to create the relation between two tables in Android Studio using Sqlite. I have one of the tables is for users and the other one is for contacts that the user can saved
user_table(userId, fullName, email, password, phoneNumber)
contacts_table(contactId, contactName, contactPhoneNumber)
I need to let the user enter as many contacts as it wants to, so when he login into its session will display all the contacts stored under that user. I couldn't figure it how to make the relation between the tables, and end it up create two separated tables, so different user can see the same contacts.
CodePudding user response：
You say that "user enter as many contacts as it wants to" and then say "so different user can see the same contacts".
In this case a many-many relationship can be used.
This involves using a 3rd table that maps a user to a contract and also a contact to a user. Thus a user can have many contacts and a contact can have many users.
Such a table has many names such as an associative table, a mapping table, a reference table ....
Such a table has two columns (or more) one of which holds a reference/map to the user and the other a reference/map to the contact. The map/reference being something that uniquely identifies the user and also the contact. It would appear that userId and contactId probably have this attribute.
So you could for example have
CREATE TABLE IF NOT EXISTS user_contact_map (userId_map INTEGER, contactId_map INTEGER, PRIMARY KEY(userId_map, contactId_map));
However, this is prone to references/mappings .... being incorrect so you can introduce rules that enforce the integrity of the references (referential integrity) by the use of Foreign Key constraints.
So instead you could, for example, have :-
CREATE TABLE IF NOT EXISTS user_contact_map (
userId_map INTEGER REFERENCES user_table(userId) ON DELETE CASCADE ON UPDATE CASCADE,
contactId_map INTEGER REFERENCES contacts_table(contactId) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY(userId_map, contactId_map)
);
This, for example, says that the value of the userId_map column MUST be value that exists in the userId column of the user_table otherwise an error is raised.
In addition to this contraint/rule the ON DELETE CASCADE (there are other options but CASCADE option is perhaps the most useful) says that should the parent (user_table row or contacts_table row respectively) be deleted then the deletion will be cascaded to the children and thus that the rows in the user_contact_map will be deleted.
In the case of ON UPDATE CASCADE, then if the userId or the contactId is changed, then that change will be cascaded to the children in the user_contact_map table.
As an example of the functionality described above consider:-
DROP TABLE IF EXISTS user_contact_map;
DROP TABLE IF EXISTS user_table;
DROP TABLE IF EXISTS contacts_table;
CREATE TABLE IF NOT EXISTS user_table(userId INTEGER PRIMARY KEY, fullName TEXT, email TEXT, password TEXT, phoneNumber TEXT);
CREATE TABLE IF NOT EXISTS contacts_table (contactId INTEGER PRIMARY KEY, contactName TEXT, contactPhoneNumber TEXT);
CREATE TABLE IF NOT EXISTS user_contact_map (userId_map INTEGER, contactId_map INTEGER, PRIMARY KEY(userId_map, contactId_map));
INSERT OR IGNORE INTO user_table VALUES
(1,'Fred Bloggs','[email protected]','pasword',' 00 123 456 789')
,(2,'Mary Bloggs','[email protected]','pasword',' 00 231 456 789')
,(3,'Jane Bloggs','[email protected]','pasword',' 00 321 456 789')
,(4,'John Bloggs','[email protected]','pasword',' 00 000 456 789')
;
INSERT OR IGNORE INTO contacts_table VALUES
(1,'C1',' 00 987 654 321')
,(2,'C2',' 00 987 654 321')
,(3,'C3',' 00 987 654 321')
,(4,'C4',' 00 987 654 321')
,(5,'C5',' 00 987 654 321')
,(6,'C6',' 00 987 654 321')
,(7,'C7',' 00 987 654 321')
;
INSERT OR IGNORE INTO user_contact_map VALUES
(1,6),(1,4),(1,2) /* 2 contacts for Fred */
,(2,1),(2,3),(2,5),(2,7) /* the other 4 contacts for Mary */
,(3,1),(3,2),(3,3),(3,4),(3,5),(3,6),(3,7) /* Jane has every contact */
/* John has no contacts */
;
SELECT * FROM user_table
JOIN user_contact_map ON user_table.userId = user_contact_map.userId_map
JOIN contacts_table ON user_contact_map.contactId_map = contacts_table.contactId
;
This when run will result in :-
- as can be seen there are the expected 3 rows (contacts) for Fred, 4 for Mary, 7 for Jane and 0 for John.
You could also, for example use something like :-
SELECT user_table.*,count(*) AS number_of_contacts, group_concat(contactName)
FROM user_table
JOIN user_contact_map ON user_table.userId = user_contact_map.userId_map
JOIN contacts_table ON user_contact_map.contactId_map = contacts_table.contactId
GROUP BY userId
;
Which would result in :-
Working Example in Android
The following is a working example that reflects what is shown above. Culminating in the two results (screen images above) but written to the log.
First is the DatabaseHelper (class that extends SQLiteOpenHelper) which has all the code relevant to the database that creates the tables, provides functions for the insertion of data and for the two extracts that return a Cursor:-
class DatabaseHelper extends SQLiteOpenHelper {
public static final String DATABASE_NAME = "the_database.db";
public static final int DATABASE_VERSION = 1;
public static final String TABLENAME_USER = "user_table";
public static final String USERID_COLUMN = "userId";
public static final String FULLNAME_COLUMN = "fullName";
public static final String EMAIL_COLUMN = "email";
public static final String PASSWORD_COLUMN = "password";
public static final String PHONENUMBER_COLUMN = "phoneNumber";
private static final String TABLECREATESQL_USER = "CREATE TABLE IF NOT EXISTS " TABLENAME_USER
"("
USERID_COLUMN " INTEGER PRIMARY KEY"
"," FULLNAME_COLUMN " TEXT"
"," EMAIL_COLUMN " TEXT"
"," PASSWORD_COLUMN " TEXT"
"," PHONENUMBER_COLUMN " TEXT"
");";
public static final String TABLENAME_CONTACTS = "contacts_table";
public static final String CONTACTID_COLUMN = "contactId";
public static final String CONTACTNAME_COLUMN = "contactName";
public static final String CONTACTPHONENUMBER_COLUMN = "contactPhoneNumber";
private static final String TABLECREATESQL_CONTACTS = "CREATE TABLE IF NOT EXISTS " TABLENAME_CONTACTS
"("
CONTACTID_COLUMN " INTEGER PRIMARY KEY"
"," CONTACTNAME_COLUMN " TEXT"
"," CONTACTPHONENUMBER_COLUMN " TEXT"
");";
public static final String TABLENAME_USER_CONTACT_MAP = "user_contact_map";
public static final String USERIDMAP_COLUMN = USERID_COLUMN "_map";
public static final String CONTACTIDMAP_COLUMN = CONTACTID_COLUMN "_map";
private static final String TABLECREATESQL_USER_CONTACT_MAP = "CREATE TABLE IF NOT EXISTS " TABLENAME_USER_CONTACT_MAP
"("
USERIDMAP_COLUMN " INTEGER REFERENCES " TABLENAME_USER "(" USERID_COLUMN ") ON DELETE CASCADE ON UPDATE CASCADE"
"," CONTACTIDMAP_COLUMN " INTEGER REFERENCES " TABLENAME_CONTACTS "(" CONTACTID_COLUMN ") ON DELETE CASCADE ON UPDATE CASCADE "
", PRIMARY KEY (" USERIDMAP_COLUMN "," CONTACTIDMAP_COLUMN ")"
");";
private static String fromClause = TABLENAME_USER
" JOIN " TABLENAME_USER_CONTACT_MAP
" ON " TABLENAME_USER "." USERID_COLUMN "=" TABLENAME_USER_CONTACT_MAP "." USERIDMAP_COLUMN
" JOIN " TABLENAME_CONTACTS
" ON " TABLENAME_USER_CONTACT_MAP "." CONTACTIDMAP_COLUMN "=" TABLENAME_CONTACTS "." CONTACTID_COLUMN;
private volatile static DatabaseHelper INSTANCE = null;
private SQLiteDatabase db;
private DatabaseHelper(Context context) {
super(context,DATABASE_NAME,null,DATABASE_VERSION);
db = this.getWritableDatabase();
}
public static DatabaseHelper getInstance(Context context) {
if (INSTANCE == null) {
INSTANCE = new DatabaseHelper(context);
}
return INSTANCE;
}
@Override
public void onCreate(SQLiteDatabase db) {
db.execSQL(TABLECREATESQL_USER);
db.execSQL(TABLECREATESQL_CONTACTS);
db.execSQL(TABLECREATESQL_USER_CONTACT_MAP);
}
@Override
public void onUpgrade(SQLiteDatabase db, int i, int i1) {
}
public long insertUser(Long id, String fullName, String email, String password, String phoneNumber ) {
ContentValues cv = new ContentValues();
if (id != null) {
cv.put(USERID_COLUMN,id);
}
cv.put(FULLNAME_COLUMN,fullName);
cv.put(EMAIL_COLUMN,email);
cv.put(PASSWORD_COLUMN,password);
cv.put(PHONENUMBER_COLUMN,phoneNumber);
return db.insert(TABLENAME_USER,null,cv);
}
public long insertUser(String fullName, String email, String password, String phoneNumber) {
return insertUser(null,fullName,email,password,phoneNumber);
}
public long insertContact(Long id, String contactName, String contactPhoneNumber) {
ContentValues cv = new ContentValues();
if (id != null) {
cv.put(CONTACTID_COLUMN,id);
}
cv.put(CONTACTNAME_COLUMN,contactName);
cv.put(CONTACTPHONENUMBER_COLUMN,contactPhoneNumber);
return db.insert(TABLENAME_CONTACTS,null,cv);
}
public long insertContact(String contactName, String contactPhoneNumber) {
return insertContact(null,contactName,contactPhoneNumber);
}
public long insertUserContactMap(long userId, long contactId) {
long rv = -1;
ContentValues cv = new ContentValues();
cv.put(USERIDMAP_COLUMN,userId);
cv.put(CONTACTIDMAP_COLUMN,contactId);
try {
rv = db.insertOrThrow(TABLENAME_USER_CONTACT_MAP,null,cv);
} catch (SQLException e) {
rv = -99; /* ooops likely FK constraint conflict */
}
return rv;
}
public Cursor getAllUserContactRows() {
return db.query(fromClause,null,null,null,null,null,null);
}
public Cursor getAllUsersWithContactCountAndContactIdCSV() {
String columns = TABLENAME_USER ".*," " count(*) AS number_of_contacts, group_concat(" CONTACTNAME_COLUMN ")";
return db.query(fromClause,new String[]{columns},null,null,USERID_COLUMN,null,null);
}
}
Second is an activity MainActivity that inserts the data and then extracts data utilising the DatabaseHelper:-
public class MainActivity extends AppCompatActivity {
DatabaseHelper dbhelper;
@SuppressLint("Range")
@Override
protected void onCreate(Bundle savedInstanceState) {
super.onCreate(savedInstanceState);
setContentView(R.layout.activity_main);
dbhelper = DatabaseHelper.getInstance(this);
long fredBloggsId = dbhelper.insertUser("Fred Bloggs","[email protected]","password"," 00 123 456 789");
long maryBloggsId = dbhelper.insertUser("Mary Bloggs","[email protected]","password"," 00 231 456 789");
long janeBloggsId = dbhelper.insertUser("Jane Bloggs","[email protected]","password"," 00 312 456 789");
long johnBloggsId = dbhelper.insertUser("John Bloggs","[email protected]","password"," 00 456 456 789");
long c1 = dbhelper.insertContact("C1"," 00 987 654 321");
long c2 = dbhelper.insertContact("C2"," 00 987 654 321");
long c3 = dbhelper.insertContact("C3"," 00 987 654 321");
long c4 = dbhelper.insertContact("C4"," 00 987 654 321");
long c5 = dbhelper.insertContact("C5"," 00 987 654 321");
long c6 = dbhelper.insertContact("C6"," 00 987 654 321");
long c7 = dbhelper.insertContact("C7"," 00 987 654 321");
dbhelper.insertUserContactMap(fredBloggsId,c6);
dbhelper.insertUserContactMap(fredBloggsId,c4);
dbhelper.insertUserContactMap(fredBloggsId,c2);
dbhelper.insertUserContactMap(maryBloggsId,c1);
dbhelper.insertUserContactMap(maryBloggsId,c3);
dbhelper.insertUserContactMap(maryBloggsId,c5);
dbhelper.insertUserContactMap(maryBloggsId,c7);
dbhelper.insertUserContactMap(janeBloggsId,c1);
dbhelper.insertUserContactMap(janeBloggsId,c2);
dbhelper.insertUserContactMap(janeBloggsId,c3);
dbhelper.insertUserContactMap(janeBloggsId,c4);
dbhelper.insertUserContactMap(janeBloggsId,c5);
dbhelper.insertUserContactMap(janeBloggsId,c6);
dbhelper.insertUserContactMap(janeBloggsId,c7);
if (dbhelper.insertUserContactMap(10000,-34526) < 0) {
Log.d("DBINFO","Failed to insert into user contact map result code!!!!");
}
logCursorInfo(dbhelper.getAllUserContactRows(),true);
logCursorInfo(dbhelper.getAllUsersWithContactCountAndContactIdCSV(),true);
}
@SuppressLint("Range")
private void logCursorInfo(Cursor csr, boolean closeCursorWhenDone) {
StringBuilder sb = new StringBuilder();
while (csr.moveToNext()) {
boolean firstColumn = true;
sb = new StringBuilder();
for (String column : csr.getColumnNames()) {
if (!firstColumn) {
sb.append(" : ");
}
firstColumn = false;
sb.append(column).append(" is " csr.getString(csr.getColumnIndex(column)));
}
Log.d("DBINFO", sb.toString());
}
if (closeCursorWhenDone) {
csr.close();
}
}
}
When run (for the first time) then the results, as per the log, are:-
2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is 00 123 456 789 : userId_map is 1 : contactId_map is 6 : contactId is 6 : contactName is C6 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is 00 123 456 789 : userId_map is 1 : contactId_map is 4 : contactId is 4 : contactName is C4 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is 00 123 456 789 : userId_map is 1 : contactId_map is 2 : contactId is 2 : contactName is C2 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.488 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is 00 231 456 789 : userId_map is 2 : contactId_map is 1 : contactId is 1 : contactName is C1 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.489 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is 00 231 456 789 : userId_map is 2 : contactId_map is 3 : contactId is 3 : contactName is C3 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.489 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is 00 231 456 789 : userId_map is 2 : contactId_map is 5 : contactId is 5 : contactName is C5 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is 00 231 456 789 : userId_map is 2 : contactId_map is 7 : contactId is 7 : contactName is C7 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 1 : contactId is 1 : contactName is C1 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 2 : contactId is 2 : contactName is C2 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.490 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 3 : contactId is 3 : contactName is C3 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.491 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 4 : contactId is 4 : contactName is C4 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.491 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 5 : contactId is 5 : contactName is C5 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.492 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 6 : contactId is 6 : contactName is C6 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.492 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : userId_map is 3 : contactId_map is 7 : contactId is 7 : contactName is C7 : contactPhoneNumber is 00 987 654 321
2022-05-25 09:19:38.494 D/DBINFO: userId is 1 : fullName is Fred Bloggs : email is [email protected] : password is password : phoneNumber is 00 123 456 789 : number_of_contacts is 3 : group_concat(contactName) is C6,C4,C2
2022-05-25 09:19:38.494 D/DBINFO: userId is 2 : fullName is Mary Bloggs : email is [email protected] : password is password : phoneNumber is 00 231 456 789 : number_of_contacts is 4 : group_concat(contactName) is C1,C3,C5,C7
2022-05-25 09:19:38.494 D/DBINFO: userId is 3 : fullName is Jane Bloggs : email is [email protected] : password is password : phoneNumber is 00 312 456 789 : number_of_contacts is 7 : group_concat(contactName) is C1,C2,C3,C4,C5,C6,C7
CodePudding user response：
Use the foreign key constraint. To do so you need to have a primary key in your parent table (here 'user_table') and then define a foreign key constraint in your child table (here 'contacts_table').
Here's your slqite Script to do so:
CREATE TABLE user_table (
user_pk INT PRIMARY KEY AUTOINCREMENT,
fullname TEXT,
email TEXT,
password TEXT,
phoneNumber INT,
);
CREATE TABLE contacts_table(
user_fk INT NOT NULL,
contact_id INT PRIMARY KEY AUTOINCREMENT,
contactName TEXT,
contactPhoneNumber INT,
CONSTRAINT user_fk_in_contacts_table
FOREIGN KEY (user_fk)
REFERENCES user_table (user_pk)
);
So you should be able to distinguish between the contacts of two users with the help of the user_fk column in contacts_table (it would indicate which user's contact that particular row)
I believe that this was a rather simple concept in play and that you should study more about databases in general if you want to build complex applications.
Moreover start using a more uniform naming conventions: like there is no "s" in user_table but there is one in contacts_table. Your table name is in snake case but the column names are in Camel Case. Going on these may lead to typos in your code even if the slightest negligence is shown.
Thank you
Over and out!