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:
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(
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
More content: http://CalebCurry.com
Amazing Web Hosting - http://bit.ly/ccbluehost (The best web hosting for a cheap price!)
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.
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
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.
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!!!!!!!!
Russian women it is unique essences. They put family above al. They are always ready to offer the outstanding career to be with the husband and children. If you will give her a little attention, love and care, if you will show her that you appreciate her as the wife and the mistress of the house, will admire with her beauty and wit, she will be true to you to the death. The grateful woman will make you in 100 times more happily if you estimate her diligence and will love her. I wish you to meet on my site the fine Russian girl who becomes to you the excellent wife and the partner, who will fill your life with love and happiness!
Marriage agency City of Russian Brides is an dating and marriage service for singles who wish to meet most beautiful Ukrainian and Russian ladies for correspondence and marriage. We provide true pictures and information of real, beautiful, sexy, intelligent, educated ladies. Welcome to A City of Brides - online photo personals mail order bride catalog of single women from Russia looking for dating and marriage worldwide. Please, bookmark our site now Our database is frequently updated. So, please visit our profiles gallery single women now. And dont forget to visit us next time.
Over recent years in the internet more and more myths emerge connected with Russian brides (under a term ‘Russian’ one should understand Ukrainian, Belarussian and Moldavian girls. We will try to explain where there is truth and where there is invention. In this respect many characteristics of Ukrainian girls and mysterious traits of a Russian soul will become understandable for you. Facts, statistics, opinion of experts, interesting articles collected in a FAQ format – you can find all this on our site.