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!!!!!!!!
Marin Community Foundation and Asian Americans/Pacific Islanders in Philanthropy.
Overall, US-born Muslims make up the largest percentage at 34% of all Muslims in the Bay Area, followed by 14% born in Pakistan, 11% in Afghanistan, 10% in India, 3% in Egypt and 2% each in Iran, Jordan, Palestine and Yemen.
Silicon Valley Pakistani-American by the Numbers:
There are 35,000 Pakistani-born Muslims in San Francisco Bay Area, or 14% of the 250,000 Muslims who call the Bay Area home, according to the study. Bay Area Muslim community constitutes 3.5 percent of the area’s total population and is one of the highest concentrations of Muslims in the country.
As of 2013, South Asian Muslims, including Pakistanis, have the highest income levels, with nearly half (49%) of them having a household income above $100,000. In comparison, those groups with the lowest proportion of household incomes above $100,000 were Hispanic Muslims (15%), Afghans (10%), and African American Muslims (10%).
The Bay Area Muslim community is very diverse in terms of race and ethnicity:
South Asians (30%)
African Americans (9%)
Asian/Pacific Islanders (7%)
Based on the survey findings, the majority of Muslims live in the following three counties:
and Contra Costa (12%)
Thousands of Pakistan-born techies are working at Apple, Cisco, Google, Intel, Oracle and hundreds of other high-tech companies from small start-ups to large Fortune 500 corporations. Pakistani-Americans are contributing to what Erik Brynjolfsson and Andrew McAfee describe as "The Second Machine Age" in a recent book with the same title.