Video is ready, Click Here to View ×


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://eepurl.com/-8qtH

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