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

Oracle SQL Tutorial 20 - How to Create Composite Primary Keys

145 ratings | 10030 views
This video is going to be a tutorial on how to create composite and compound keys. The difference between a composite and compound key is that a composite key can consist of any columns while a compound key has to consist of columns that are all keys themselves. We will be working with a compound key because we are going to be using the an intermediary table that has two foreign keys. The combination of both of the keys have to be unique. First, if we have any other CREATE TABLE commands, we are going to comment those out. We will space out the CREATE TABLE to have each column on a line, then we will add constraints as needed. CREATE TABLE project_users( project_id, user_id ) Now, let's add the data types: CREATE TABLE project_users( project_id NUMBER, user_id NUMBER } Now, what about some column attributes? I'm going to make both of the columns NOT NULL because we always want the rows to have a user and a project: CREATE TABLE project_users( project_id NUMBER NOT NULL, user_id NUMBER NOT NULL } Now, let's add the foreign key constraints. Now, what do we name these? We are going to add a primary key that covers both of these columns, so I'm going to be a sinner and not give these constraints names: CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id), user_id NUMBER NOT NULL REFFERENCES users (user_id) ) Now, the way we have it now is that if we delete a project in the project table, and there are any rows in the project_users table, it will throw an error and prevent deletion. I would prefer for it to also delete any project members. That would make sense because if you delete a project we want it to delete the associate between that project and certain users. The same goes for if we delete a user, we want their association with a certain project to be deleted. To do this, we need to add the ON DELETE command: CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, ) Finally, let's learn how to create a compound or composite key. literally, the only difference is that you put a comma and add the second table inside of the parenthesis. CREATE TABLE project_users( project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE, user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE, CONSTRAINT project_users_pk PRIMARY KEY (project_id, user_id) ) Now, the combination of project_id and user_id cannot be null, is always unique, and has an index. The only thing we should do now is add a few indexes. We aren't done yet...In the next video we are going to figure out what columns would benefit from indexes and we'll add them to it. ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ 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 (12)
Kamran Memmedli (1 month ago)
till now i understood 90% of stuffs, still have little theoritical problems with relationship constraints, but overall my development is good))
SplitSniper7 (6 months ago)
It took me until a second viewing to actually hear what you were talking about regarding ON DELETE CASCADE.
Dave Thatcher (6 months ago)
You're so weird haha, but great info. Thank you it was very helpful. You made it very simple and easy to follow.
Lugmillord (7 months ago)
What's the difference between using FOREIGN KEY REFERENCES and just REFERENCE? I'm assuming you use the latter one when the referenced column is not a primary key?
ahmad ali (1 year ago)
thank you
Hi, I want to implement this using Oracle SQL Developer Data Modeler, Only view, But I can't to edit. DDL.
nshusa A (1 year ago)
How do you use a composite primary key as a foreign key in another table?
Mohit Monpara (1 year ago)
I have one doubt: if I want to add foreign key reference from another table in different database, then what we could do?
Lindel Marshall (1 year ago)
was very helpfull
Lindel Marshall (1 year ago)
Thanks so much
Timucin Acar (1 year ago)
Looks like you stoped these videos sad though i really liked them but anyway thanks for the couple ones you already did :D
Caleb Curry (1 year ago)
I hope to make more. I've paused for now to study.

Would you like to comment?

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