How to define a PK using Data Modeler?

Primary Key (PK) identifies uniquely each row in a table. There can never be two tuples with same values in PK.

There are two ways of defining a PK: a natural key or a surrogate key. I would say that if you can find a natural key that is always better. But it is not always possible and therefore we can also use surrogate PKs. Surrogates are usually sequence numbers that has no meaning to the business people, the end users.

Let’s have an example of an entity for all the users in a company.



1. Defining a natural PK

What I want is that there will never be more than one user with the same username. If this means that there can NEVER be two with the same username, I would define the PK like this:


You can see the PK definition from Unique Identifiers tab:


And by clicking the Properties button (XYZ and a pen) you can see and edit the properties for this unique identifier.

You can for instance change the name


Or see what attributes or relationships are involved in this PK. You can add or remove attributes or relationships to/from the PK using the arrows pointing to right and left. And you can change the order of the PK elements using the arrows pointing up and down.


If the requirement would be that there can never be two users at the same time with a same valid username I might think a bit. If I am sure that a pair (Username, Valid) would be enough as a PK, it means that I can only have max two users with the same Username: one with Valid=true and another one with Valid=false, I could define that as the PK, but what happens when the second one needs to be disabled eg. Valid changed to false? Then I would have two tuples with same values in Username and Valid, that would not work because that is my PK. So having a PK with just Username and Valid would not be quite smart. I could also use Created date in PK but would that be smart? No. Having a PK (Username, Valid, Created) would help me to disable a username even though there is already another of the same Username disabled or adding the third user with the same Username but it would not guarantee that only one user would have that Username at a point of time. And that was exactly what the requirement was.

I could add BeginDate and EndDate as attributes:


But that would not help me because I could not use those attributes in the PK because I should be checking that the two rows are not valid at the same time, eg. comparing the BeginDates and EndDates for all rows having the same Username. Of cause I could check those attributes using PL/SQL and check that only one row is valid at a time but that is not a PK.

No wonder many people end up to the conclusion that the requirement means that there can NEVER be two users with the same Username :-) Then the PK (Username) would be sufficient.

But what if the requirement really is that there cannot be more than one at the same point of time? Then I would use surrogate as the PK and use PL/SQL (trigger) for checking the rest.

2. Defining a surrogate PK

There are two ways to define a surrogate PK: manually or automatically.

If I want to create it manually, I simply define an attribute for it and define it as the PK. My recommendation is that if this is the way you want to work, start by defining a Domain for surrogates and always use that when defining a surrogate PK manually. Otherwise defining a surrogate PK manually is done just like I explained earlier on Defining a natural PK.

You can also define a surrogate PK automatically. In entity properties enable Create Surrogate Key and when you engineer to relational model, this surrogate key and the surrogate column are created automatically.


If using surrogate keys is the preferred way of working for you, you might consider enabling that property so that the default would be Create Surrogate Key enabled.


Remember that you can define the naming standard for the surrogate key and the column created in Design Properties (right-click on the Design name and select Properties):


This was a quick look to PKs. A natural key is always the best and being able to define one you must know the requirements and understand them. If a natural key does not work, then a surrogate key is an option.


In June I attended my second KScope and as expected it was an excellent event! This year was even better than last because I had so many new friends I did not have last year :-)

The Kscope15 was held in Hollywood, Florida June 21-25.

On Sunday I attended the “Database Development for Cloud” session by Jeff Smith and Kris Rice. I also attended the speakers’ reception, welcome reception and a user group leader reception. After all that I was pretty ready to sleep, there it a 7h time difference to my own timezone :-)

On Monday I attended Tom Kyte’s session about SQL and Chet Justice’s session about “How to Model Your Data”. Then I gave my own session with a title “What’s New with SQL Developer Data Modeler” and gave away two copies of my new book. I had my two sons in the audience and their impression after the session was that I know “all” :-D Thank you for attending my session! After lunch I gave a couple of interviews. I was told that I am very natural in front of a camera, I could not disagree more :-) The General Session was the funnies ever! And next year we will go to Chicago for Kscope16! After the general session it was time for some mojitos in the Exhibition Hall. Then the ACE reception by the pool at one of the hotels near by and the networking events sponsored by OTN. Too bad I do not like beer because Jeff Smith organized an amazing beer tasking Networking Hopportunity event :-( Well, I still had fun :-)

On Tuesday I attended Kent Graziano’s “Introduction to Oracle SQL Developer Data Modeler” session and was one of the panelists at the Database Development panel led by Tim Gorman. In the afternoon I attended Michelle Kolbe’s session “Overview of Dimensional Modeling Principles Using Oracle SQL Developer Data Modeler” and Jeff Smith’s “Tuning with Oracle SQL Developer”. Then it was time for some drinks and food and tattoos on Tattoo Tuesday event. Then I had a meeting and a nice evening with friends. Thank you MindStream Analytics for your amazing hospitality!

On Wednesday I had plans to attend presentations by Pete Sharman, Alex Zaballa and Melanie Caffrey but I missed them because I had a busy morning meeting people and then helping Ashley Chen with the Data Modeler Hands-On-Lab. Several people showed up without their laptops so we decided that Ashley will handle the lab and I will do a demo for those without a laptop. I had so much fun! Thank you Ashley for letting me to be part of that! After the HOL I ran to the WIT roundtable where I was moderating one of the tables. Our topic was “Best Qualities of Female Tech Leaders”. I must say I enjoyed the conversation and based on the comments I heard afterwards so did the rest of our table. Thank you for attending and thank you for letting me to be the moderator! In the evening there was a White Party at Nikki Beach. My sons got tickets from Tom and Mel and we did quite a lot of shopping just for the party but just at the last minute we heard the age limit for the party was 21 and doh… No party for us (me and my sons). Instead we went for a nice dinner close to the party venue and early back to the hotel. I was kind of sad because I heard there was a lot of dancing involved the party and I simply love dancing :-( Well, you cannot always get everything :-)

On Thursday I slept a bit later and only attended the closing session and then some meetings afterwards.

A busy but wonderful week I must say! Thank you all those wonderful people who made this event happen: Natalie, Monty, Crystal, Lori, Mike, Opal, Sarah, Danny, Martin, Mia, Cameron, Lauren, Melissa and everybody else whose names I just do not remember right now because of my stupid jet lag ;-)

It was wonderful to be part of it and it was really great to meet so many friends again! See you next year in Chicago!

ilOUG Tech Days 2015

I finally have time to sit down and write about my experience at the ilOUG Tech Days 2015!

The Tech Days took place in Haifa, Israel 9.-10.6.2015. I had three sessions to present: Database Design in Agile Projects, Reporting with Oracle SQL Developer Data Modeler and Is Your Database Secured? This was my second time in Israel (two years ago I attended the ilOUG Tech Days 2013 in Jerusalem) so I knew a little bit what to expect: fantastic food, great questions and surprisingly many women in the audience. And of course Ami and Liron from the user group to do everything they can to make you feel welcome and comfortable. And they really do a great job!

During my Data Modeler sessions I gave some copies of my new book away and signed them. The Data Modeler talks were well received but I think the security one was the best. It is not an easy task to go to Israel and talk about security: those guys know everything about security! I even had some solders is my audience! But I think the presentation went well and the audience liked my ideas and comment on the topic. I had many excellent questions and many people thanked me afterwards. My favourite was the gentleman who asked me if I will write my next book on security… Who knows what happens…

I think the conference was excellent and I really hope I will have a chance to go to Israel again. Thank you for the “Bambas”! And halvas!

Which tables are partitioned? Which tables are index organized?

This requirement calls for reporting…

Start Search by right-clicking the physical model name in Browser and by selecting Search.


Select Advanced Mode and select Table as Object Type.


Press Add Property.

Select Partitioned as a Property and type “yes” to the value field.


Press Search.


Now you have a list of partitioned tables in physical model Oracle 12c.

Maybe you want to have a nice report of them?

Press Report.


You can generate the report as it is


or you can use Custom Templates.



How about my IOTs?

In Advanced Mode select Organization as Property and type “Index” on value field.


Press Search. Press Report. And either generate the report as is or use one of your Templates.



And if I want to have tables that are both partitioned AND index organized?


First select Partitioned=yes. Then press Add Property again and select Organization=index. Remember to set the logical operator to AND. Then press Search.


And generate the report wanted.

Note: If you run the report in Microsoft Excel format you are able to edit the content in Excel and then upload it back to Data Modeler.


BGOUG, thank you!

It is easy to say BGOUG is one of my favourite conferences. Not only because Milena is a dear friend, the venue is always wonderful, food is excellent etc but because I simply love the Bulgarian audience! They are extremely interested in learning, they ask a lot of questions and the best for a shy Finn like me is that they give you wonderful face-to-face feedback.

After my first session a gentleman came to me and he said he saw my session two years ago and it changed his life for better: now he knows a better way to talk to the end users to get better requirements to be able to create better databases and applications for end users. Another person came to me and said he really hope I will be there in November to share my knowledge again because he learns so much in every presentation. A lady wrote to me that she admires my work and enjoys my sessions very much. Etc.

I am usually very bad with feedback: good or bad. But these people can give it in a way I do not feel embarrassed but honored and privileged. Thank you all for your nice comments! You do not know how much it means to me! I was really tired after the conference (I just realized I have not had any free weekends for two months!) but extremely happy: my travelling and speaking has a meaning! Thank you!

And Thank you Philip Stoyanov for the fantastic speech!


Closing the FY

It’s the end of Oracle fiscal year and now it’s time to close the books for that fiscal. I did have a busy year. I wrote my first book

I attended two OTN Tours. I had four sessions at the Oracle Open World, I taught the first time at the OU Expert Summit and much more. And being honest I was very sick for 4 months so I actually only had 8 months to do that all :-) I spoke in (at least) these events:

21.-26.5.2015 OTN EMEA Tour: Portugal, Jordan, South Africa

16.5.2015 Wildcard Unconference, Riga, Latvia

23.4.2015 Oracle University Expert Summit, Bucharest, Romania

22.4.2015 RoOUG, Bucharest, Romania

12.-14.3.2015 OUGN, Norway

22.1.2015 Riga Dev Day, Riga, Latvia

18.–20.11.2014 DOAG Deutsche ORACLE-Anwendergruppe e.V., Nüremburg, Germany

13.11.2014 Oracle Roadshow, Turku, Finland

6.11.2014 Oracle Roadshow, Espoo, Finland

28.10.2014 Oracle Roadshow, Tampere, Finland

20.- 23.10.2014 Nordic ACE Director Tour: Sweden, Norway, Denmark, Finland 28.9.–2.10.2014 Oracle OpenWorld, San Francisco, USA

22.-26.6.2014 KScope14, Seattle, USA

13.-15.6.2014 BGOUG, Bulgaria

5.-6.6.2014 Harmony, Finland

20 events… wow! But I did have a wonderful year and I enjoyed every moment of it. Thank you for inviting me to speak in those amazing events and thank you OTN and the ACE program for funding many of my tours! Thank you Oracle Press for asking me to write the book.  I wonder what will happen this year…

BGOUG, Day 2

It’s the second day of BGOUG conference. The first day was excellent and was highlighted by a great party in the evening: good food and drinks, great company and lots of dancing! Not to mention the nice speech Philip Stoyanov gave at the party before we had a raffle for two lucky winners for a copy of my book :-)

The second day started with a breakfast at 8 and the sessions at 10. I have been working the whole morning (answering questions from customers about Data Modeler, preparing my sessions etc) and I just realized that while you are travelling you do not make a difference if the day is a weekday or Saturday/Sunday. It’s 11.30 am now and I was wondering why people do not reply my emails. Now I know: it’s Saturday :-)

Today I will have two sessions: one about comparing designs and database (at 14:00) and another one about reporting (at 15:00). Both sessions are in Plovdiv 3. I hope to see you there!



Oracle SQL Developer Data Modeler for Database Design Mastery available in EMEA!

My book is finally available in EMEA:


M-H Education EMEA

ilOUG Tech Days 2015

Tomorrow it starts: the ilOUG Tech Days 2015 in Haifa, Israel. I travelled all day yesterday to be here in time to attend the fantastic guided tour by Ika, the best guide in Israel. We had a wonderful day at the Nazareth area. After the tour I needed to work a little and then to dinner. Then I prepared my slides for tomorrow. I will be talking about agile database design, reporting with Data Modeler and about database security. And I will give one copy of my new book away in each Data Modeler session…

Hope to see you tomorrow! Now it’s time to go to bed…


Get every new post delivered to your Inbox.