January 9, 2019 at 4:51 pm
Hi, i have to build a database for my university coursework, i have 5 tables and 1 of them has 2 foreign keys connecting to 2 other tables. I have it set out sort of like this,
Table 1(
Primary Key#1
);
Table 2(
Primary Key#2
);
Table 3(
Foreign Key#1
Foreign Key#2
);
Is there any way to make it so inputting a key into both primary keys in table 1 and 2 will automatically input the same key into the table 3 foreign keys?
My actual code if needed is below;
CREATING THE TABLES
SET FOREIGN_KEY_CHECKS=0;
CREATE TABLE Suspect(
SuspectID int auto_increment PRIMARY KEY,
FirstName varchar(255),
SecondName varchar(255),
Address varchar(255),
Gender varchar(255),
Age_Range varchar(255),
Self_Defined_Ethnicity varchar(255),
Officer_Defined_Ethnicity varchar(255)
);
CREATE TABLE Conviction(
CrimeID int auto_increment PRIMARY KEY,
Crime_Type varchar(255),
Last_Outcome_Catagory varchar(255),
Date date,
Outcome_Linked_To_Object_Of_Search varchar(255),
Outcome varchar(255),
Removel_Of_More_Than_Just_Outer_Clothing varchar(255),
SuspectID int NOT NULL,
OfficerID int NOT NULL,
FOREIGN KEY (SuspectID) REFERENCES Suspect(SuspectID),
FOREIGN KEY (OfficerID) REFERENCES Officer(OfficerID)
);
CREATE TABLE LocationOfConviction(
Longitude int,
Latitude int,
Location varchar(255),
LSOACode int,
LSOAName varchar(255),
SuspectID int PRIMARY KEY REFERENCES Suspect(SuspectID)
);
CREATE TABLE Officer(
OfficerID int auto_increment PRIMARY KEY,
FristName varchar(255),
SecondName varchar(255),
Reported_By varchar(255),
Part_Of_Policing_Operation varchar(255),
Legislation varchar(255),
Object_Of_Search varchar(255)
);
CREATE TABLE Prosecution(
OfficerID int,
SuspectID int,
FOREIGN KEY (OfficerID) REFERENCES Officer(OfficerID),
FOREIGN KEY (SuspectID) REFERENCES Suspect(SuspectID),
Prosecuted varchar(255)
);
CONVICTION TABLE (THE ONE WITH THE FOREIGN KEYS)
INSERT INTO Conviction VALUES(
'',
'Violence',
'Suspect Arrested',
'2017-09-00',
'FALSE',
'Suspect Arrested',
'FALSE',
'',
''
);
OFFICER TABLE (TABLE 1 WITH FIRST PRIMARY KEY)
INSERT INTO Officer VALUES(
'',
'Joe',
'Newton',
'Lancashire Constabulary',
'',
'Misues of Drugs Act 1972 (Section 23)',
'Controlled Drugs'
);
INSERT INTO Officer Values(
'',
'Sam',
'Webster',
'Lancashire Constabulary',
'',
'Police and Criminal Evidence Act 1984 (Section 1)',
'Article for use in theft'
);
INSERT INTO Officer Values(
'',
'Cameron',
'Singleton',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen Goods'
);
INSERT INTO Officer Values(
'',
'Daniel',
'Shaw',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen Goods'
);
INSERT INTO Officer Values(
'',
'Ayden',
'Collins',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Article for use in theft'
);
INSERT INTO Officer Values(
'',
'Charlotte',
'Whatron',
'Lancashire Constabulary',
'',
'Misues of Drugs Act 1971 (Section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'James',
'Fisher',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Article for use in theft'
);
INSERT INTO Officer Values(
'',
'Michael',
'Schofield',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Daniel',
'Connelly',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'Lawrence',
'Andrew',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Neil',
'Mullen',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Kiera',
'Steadman',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Vlad',
'Harper',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Roy',
'Queen',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Oliver',
'Harper',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Offensive weapons'
);
INSERT INTO Officer Values(
'',
'Stewart',
'Allman',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Cillian',
'Velez',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Ebony',
'Beard',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'Reagan',
'Prosser',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Mohammod',
'Palmer',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Darleene',
'Povey',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Article for use in theft'
);
INSERT INTO Officer Values(
'',
'Roger',
'Stein',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Article for use in theft'
);
INSERT INTO Officer Values(
'',
'Jimmy',
'Hnedrix',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'Lorraine',
'Allison',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'Billie-Joe',
'Armstrong',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'Jackie',
'Coulson',
'Lancashire Constabulary',
'',
'Polcie and Criminal Evidence Act 1982 (Section 1)',
'Stolen goods'
);
INSERT INTO Officer Values(
'',
'George',
'Hester',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'Lucy',
'Regan',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer Values(
'',
'James',
'Conway',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled drugs'
);
INSERT INTO Officer VALUES(
'',
'Lincoln',
'Burrows',
'Lancashire Constabulary',
'',
'Misuse of Drugs Act 1971 (section 23)',
'Controlled Drugs'
);
SUSPECT TABLE (TABLE 2 WITH SECOND PRIMARY KEY)
INSERT INTO Suspect VALUES(
'',
'Anthonie',
'Watkins',
'48 Hindlead Road',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'Asian'
);
INSERT INTO Suspect VALUES(
'',
'Jo',
'Taylor',
'181 Maidstone Road',
'Female',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Mark',
'Tinsley',
'82 South Western Terrace',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Adam',
'Donald',
'80 Stamford Road',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Matthew',
'Cassell',
'71 London Road',
'Male',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Chardonnay',
'Whitworth',
'69 Fulford Road',
'Female',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Keenan',
'ONeil',
'72 Whitchurch Road',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Reece',
'Burke',
'29 Oxford Road',
'Male',
'16-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Adeel',
'Mcmanus',
'91 Henley Road',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Oskar',
'Chavez',
'18 Stroude Road',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Nathaniel',
'Milne',
'114 St Omers Road',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Theresa',
'Phillips',
'60 North Promenade',
'Female',
'',
'',
''
);
INSERT INTO Suspect VALUES(
'',
'Darrell',
'Prosser',
'87 Newgate Street',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Brad',
'Bender',
'131 St Omers Road',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Lenny',
'Mcconnell',
'70 Buckingham Road',
'Male',
'18-24',
'Asian',
'Asian/Asian British - Pakistani'
);
INSERT INTO Suspect VALUES(
'',
'Keelan',
'Herman',
'112 Park Avenue',
'Male',
'',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Siya',
'Adams',
'67 Dunmow Road',
'Female',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Carlton',
'Manning',
'123 Boroughbridge Road',
'Male',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Hubert',
'Ford',
'82 Thirsk Road',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Bill',
'Gates',
'49 Oxford Road',
'Male',
'18-24',
'Asian/Asian British - Pakistani',
'Asian'
);
INSERT INTO Suspect VALUES(
'',
'Hania',
'Rees',
'104 Gloddaeth Street',
'Female',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Rose',
'Tyler',
'88 Overton Circle',
'Female',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'David',
'Tennant',
'47 Nith Street',
'Male',
'Over 34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Matt',
'Smith',
'93 Main Road',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Karen',
'Gillen',
'110 Harrogate Road',
'Female',
'18-24',
'Asian/Asian British - Pakistani',
'Asian'
);
INSERT INTO Suspect VALUES(
'',
'Jenny',
'Lugo',
'23 Graham Road',
'Female',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Robert',
'Downey JR',
'89 Manor Way',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Stefano',
'Sharpless',
'105 Thames Street',
'Male',
'Over 34',
'',
'White
'
);
INSERT INTO Suspect VALUES(
'',
'Kamal',
'Leal',
'21 Grey Street',
'Male',
'25-34',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
INSERT INTO Suspect VALUES(
'',
'Jacob',
'Cooke',
'109 Ockham Road',
'Male',
'18-24',
'White - English/Welsh/Scottish/Northern Irish/British',
'White'
);
January 10, 2019 at 1:55 am
You could probably do it with a trigger, though will have to be complex and on both tables, and I don't know how you plan to have it automatically figure out which key goes with which key.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
January 10, 2019 at 9:48 am
I don't think that you want to do this in general. I could see doing this as part of an application, but the application would probably feed the information into a stored procedure which would handle all of the inserts.
Drew
J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply