Hi i'm doing a project for my uni dissertation and I'm slightly stuck! I have created my tables as follows but i'm not sure if the foreign keys reference the primary keys properly and even if they are correct how do I insert into the primary key in one table and it automatically inserts into the corresponding foreign key in the other table?
CREATE DATABASE TASTYWET
USE TASTYWET;
==================================================================================
CREATE TABLE CLIENTS
( CLIENTID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
CMPNY_NAME VARCHAR(30) NOT NULL,
CONTACT_NO INT(15) NOT NULL,
FNAME VARCHAR(15) NOT NULL,
LNAME VARCHAR(15) NOT NULL,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20) NOT NULL,
POSTCODE VARCHAR(10) NOT NULL,
EMAIL VARCHAR(20)
);
CREATE TABLE AGENCY_MEMBER
( MEMBERID INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
HOUSE_NO CHAR(6) NOT NULL,
STREET VARCHAR(30) NOT NULL,
TOWN VARCHAR(20) NOT NULL,
POSTCODE VARCHAR(10) NOT NULL,
DOB DATE NOT NULL,
EMAIL VARCHAR(20),
CONTACT_NO INT NOT NULL, //? HOW DO I STORE A MOBILE NUMBER?
STAGENAME VARCHAR(15),
EXPRNCE_IN_YEARS INT NOT NULL,
SEX CHAR(1) NOT NULL,
NI_NO VARCHAR(12) NOT NULL
);
CREATE TABLE SPECIALIST_AREA
( MEMBERID INT UNSIGNED NOT NULL
//CONSTRAINT S_A REFERENCES AGENCY_MEMBER(MEMBERID),
SPECIAL_AREA VARCHAR(20) NOT NULL //EG DJ, SINGER, DANCER, MC ETC.
PRIMARY KEY (MEMBERID)
);
CREATE TABLE MEMBER_HIRED
( MEMBERID INT UNSIGNED NOT NULL,
CLIENTID INT UNSIGNED NOT NULL,
HIRE_DATE DATE NOT NULL,
HOURS_HIRED_FOR INT NOT NULL
PRIMARY KEY (MEMBERID, CLIENTID) //IN MYSQL IS THIS INSTEAD OF CONSTRAINT
//REFERENCES PRIMARY KEY IN OTHER TABLE.
//IS THIS A FORM OF REFERENTIAL INTEGRITY?
);
CREATE TABLE ADMIN //CREATE TABLE FOR ADMINISTRATION PURPOSES FOR DATABASE ONLINE
( USERID INT NOT NULL PRIMARY KEY,
USERNAME VARCHAR(16) NOT NULL,
PASSWORD VARCHAR(8) NOT NULL
);
Cheers for any speedy responses!
