CODEWITHSUNDEEP
mysqldatabase
HurkNburkS
HurkNburkS

Reputation: 5510

how to create sql table with foreign keys

I am wanting to create a sqlDB on my server, I am very new to db development and have only done the odd bit of coding here an there several years ago. I am trying to build a db that has four tables that reference each other..

This is what each of the tables are created for to give you a better idea as to what I am trying to achieve.

Group this table will store hubs which users create for other users to follow.

User stores users details which are used when creating groups and joining groups

//Not sure if this next one is a good idea but hopefully someone can let me know what they thing about it userGroups this table stores all or the groups and user connections. When a user joins a group their id is stored with the group id.

Countries countries are used to help restrict the number of results that can be returned

heres the code I have created that I would like to run on my DB.. I am not sure if I have made the foreign keys correctly, any advice on that would be appreciated also.

CREATE DATABASE tempDB
USE tempDB

CREATE TABLE Group (groupID UInt32 PRIMARY KEY, userID UInt32 references User(SID), country VARCHAR(20) references Country(SID));

CREATE TABLE User (userID UInt32 PRIMARY KEY, name VARCHAR(20), country VARCHAR(20) references Country(SID));

CREATE TABLE userGroups (userID references User(SID), groupID UInt32 references Group(SID));

CREATE TABLE countries (countryName VARCHAR(20) PRIMARY KEY);

so three questions:

  1. Is the table UserGroups worth while?
  2. Have I created the foreign Keys correctly?
  3. Will this code create a DB with the 4 tables in them...

Upvotes: 2

Views: 240

Answers (2)

Naveen Kumar
Naveen Kumar

Reputation: 4601

  • Before creating any table which has a foreign key that table should be created.
  • Country(SID)- there is no table Country and field SID
  • Countries table should have a id field as primary key.
  • To use foreign keys you should specify the engine =INNODB

  • To check if tables have been created with Forign key 

    mysql> Show tables // Shows all created table
mysql> desc tableName  // to see the structure of the created table

Upvotes: 1

Ray
Ray

Reputation: 41428

If your users can be in many groups and many users can be in a group drop the column and foreign key reference to the userid field in the group table. The usergroups table handles this many-to-many mapping.

Your current setup would only make sense if say each group had also a single 'owner' user...

Also, what DB engine are you using? Innodb or myisam... If myisam foreign keys are jest like any other index without any ability to enforce constraints, therefore not really useful to try and declare.

Upvotes: 2

Related Questions