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

Oracle SQL Tutorial 14 - Column-Level and Table-Level Constraints

349 ratings | 19133 views
In the previous video we talked about adding constraints at the column-level. We made it nice and simple by only requiring a few keywords, but the problem we were having is that we could not assign a name to the constraint, which many people like to do so we can reference easily if we need to at a later time. To do this, it requires a little bit more typing, but it will give us extra flexibility and many consider it to be the higher quality approach to adding constraints. Let's go though a simple example. Let's say we have a users table with a user_id column that we want to make a primary key. We will create the table like this: CREATE TABLE( user_ id NUMBER PRIMARY KEY ) Instead of adding the PRIMARY KEY keywords after the data type, we add: CONSTRAINT user_pk PRIMARY KEY Now, we have assigned the name user_pk to this constraint. You can do the same with other constraints, such as UNIQUE. The syntax would be CONSTRAINT username_un UNIQUE. The other way to create constraints requires to put all of our constraints at the bottom of our table creation rather than inline with the column. This type of constraint is known as a table-level constraints. To make a column a primary key using table-level constraints, we add it to the CREATE TABLE command as if it is another row and use the CONSTRAINT keyword to tell Oracle that what is coming is a constraint, not a column in our table. CREATE TABLE users( user_id NUMBER, username VARCHAR2(50 CHAR), CONSTRAINT username_un UNIQUE (username), CONSTRAINT users_pk PRIMARY KEY (user_id) ) The primary differences here is that you have to put the column you are talking about in parenthesis after the PRIMARY KEY keyword. That's because it's at the end of the table and you need a way to tell it what column you are talking about. The option of putting it at the end of the table has the added benefit in this situation because if we needed to have a primary key that is the combination of multiple columns, we can do that by just adding the other column in the PRIMARY KEY parenthesis right after a comma. In summary, there are three ways to make constraints. The first is at the column level, unnamed. The second is at the column level, named. The third is at the table level, also named. In the next video we are going to create a named constraint in Oracle SQL Developer, so stay tuned and be sure to subscribe! ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Support 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 (15)
You never showed us how you got to the query builder. I am having difficulties trying to set up my database. Can anyone help?
Krishna CJ (5 months ago)
thank you sir
Elric (1 year ago)
Much appreciated. I'm in the unenviable position of being forced to make a career shift at middle age, and your series on Oracle DBA is just what I've been looking for to get me started on this path. Thanks so much.
Jonathan Ambriz (1 year ago)
My favorite part is "as you can see it's the same except TOTALLY different"
Mohamed Sassi (1 year ago)
Good job!
Binruo LYU (2 years ago)
Thanks for your tutorials
Jasmine Rose (2 years ago)
We can't add NOT NULL constraint In Table Level.
Maha (2 years ago)
I will share your channel with my CS friends, you make it so easy and understandable
Ali Ahsan (1 year ago)
yupe. He did it. I'm also gonna share this in My cs group
Jagadesh (2 years ago)
how to use not null constraint in table level constraint
Darshan .p (2 years ago)
i tried to create not null constraint at table level while creating table its showing error as invalid identifier but then i changed to create with primary key constraint table created. can anyone tell me the reason
Patty Bond (2 years ago)
Caleb, you are a hoot. Underneath the comical and charming personality is a very smart dude. I am new to programming, Oracle, etc -- except for limited knowledge of RUBY. Your videos make a difficult topic easier to comprehend and for most of your videos, I find myself having a belly laugh at least once. If you ever decide to give up this gig, you may want to consider comedy on the big screen.
Kwabena Victor (2 years ago)
Caleb you are just great. I appreciate all your efforts. Keep it up. Can you do a video that will help us to understand Oracle Linux. Preferably how to administer oracle database on Oracle Linux!!!!! Cant wait to see videos on that!!!!!!!!
Prem (2 years ago)
thanks Caleb for making Oracle SQL :)
Caleb Curry (2 years ago)
Of course! Keep watching as there are more to come! :)

Would you like to comment?

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