HomeНаука и техникаRelated VideosMore From: Caleb Curry

Oracle SQL Tutorial 18 - How to Create Foreign Keys

230 ratings | 20326 views
In this video we are going to be creating foreign keys. I highly recommend watching the previous video before you watch this one. Essentially, we are creating a very simple database for a system where we can create projects and add people to those projects. We started with the users table: --Delete the table if needed: --DROP TABLE users; CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR) UNIQUE, CONSTRAINT users_pk PRIMARY KEY (user_id) ); Now we are going to create a table for projects with a column that is a foreign key to the username. We're going to want to make this match data types: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) ) Next, we need to add the column attributes we decided on last video: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL ) NOT NULL because we want every project to have a creator, but we are not labeling UNIQUE because that means we could only have a specific username once in the table. We want to allow a user to create multiple projects. We also need to add a primary key: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (username) ) Now, the question that remains is, how can I tell Oracle that I want the username to reference the username column of the other table? We have to make a foreign key constraint. As you've learned from the previous videos, there are about three ways to create constraints. You can do it inline at the column level, unnamed. You can do it at the column level, named, and you can do it at the Table level, named. Usually the table-level is preferred, but I will quickly remind you how to do all three again. CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL REFERENCES users (username), CONSTRAINT projects_pk PRIMARY KEY (project_id) ) This works, but if we want to name it, we should do this: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL CONSTRAINT projects_users_fk REFERENCES users (username), CONSTRAINT projects_pk PRIMARY KEY (project_id) ) This works, but the preferred method is to do it at the table level: CREATE TABLE projects( project_id NUMBER, project_name VARCHAR2(50 CHAR) UNIQUE, creator VARCHAR2(50 CHAR) NOT NULL, CONSTRAINT projects_pk PRIMARY KEY (project_id), CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username) ) Great! So you've learned how to create a foreign key, now we can see it inside of Oracle SQL Developer. One important thing when it comes to foreign keys is what happens when have data in your database and you try to delete the parent row that a row in the child table references? In the next video we are going to configure that using ON DELETE. See you all then and if you enjoy this series, please do me a huge favor by liking the video and subscribing to my YouTube channel. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ HELP ME! http://www.patreon.com/calebcurry Subscribe to my newsletter: http://bit.ly/JoinCCNewsletter Donate!: http://bit.ly/DonateCTVM2. ~~~~~~~~~~~~~~~Additional Links~~~~~~~~~~~~~~~ More content: http://CalebCurry.com Facebook: http://www.facebook.com/CalebTheVideoMaker Google+: https://plus.google.com/+CalebTheVideoMaker2 Twitter: http://twitter.com/calebCurry Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
Html code for embedding videos on your blog
Text Comments (11)
truong hieu (2 months ago)
Thk from VietNam much help!!!
David Colbourn (3 months ago)
sql is fine I googled oracle data modeler
I still have doubts with the DROP thing... could you reference me to the Oracle SQL Tutorial in which you explain about that? im kinda lost...
Michael B (1 year ago)
This whole series is fantastic and very thoro Caleb I think you have it down so Thank you and i hope you will be making more tutorials the best part is you are clear and concise.
Karolm1711 (1 year ago)
hey how are you? can you help me out? im typing down the same code as u are but here its showing up the error 00904 000 - %s: invalid identifier. what should it be?
DavoStory (1 year ago)
There must be some slight difference in your code and his. I had the same problem, but then copied his code in the description and everything worked. My problem turned out to be typing: CONSTRAINT project_pk PRIMARY KEY(projects_id) instead of: CONSTRAINT project_pk PRIMARY KEY(project_id) Facepalm
Syed Yasar (1 year ago)
getting Error report - SQL Error: ORA-00905: missing keyword 00905. 00000 - "missing keyword" *Cause: *Action: please heip
Mr Fazeel (1 year ago)
I'want to make Data base of Store.that containing spare parts and tools that is use ,we should add or remove parts.. parts is specify with part no. serial no. and manufacture date and expiry date. will you please help me with some video, i m new learner im watching your videos its nice,thanks for all that videos.Stay Blessed!
Maawa Dee Jay-Jay (2 years ago)
Awesome tutorial.much appreciated
Caleb Curry (2 years ago)
Of course!
Mauro Bertozzi (2 years ago)
cancer

Would you like to comment?

Join YouTube for a free account, or sign in if you are already a member.