1z0-071 Oracle Database SQL – CONSTRAINTS

1. NULL – DEFAULT

Create table cons one call, one number. Let me query it. As expected, it doesn’t have any rows. Now, let me insert a null value. I can do it by just specifying two single quotes. Query it. We see the null value here. What this means is, while creating a table, if we just specify the column name and its data type, by default it accepts null values. Now, let me insert a number. I know it is a numerical column. Verify it. We see the null numerical value here and let’s drop the table as of now.

2. NOT NULL CONSTRAINT

Let me recreate the same table with one modification. Create table count one, call one number not null. Here I have specified that the column call one will be a not null column. This means that it will not accept any null values. Let us check that. Select star from counts one, no rows, as expected. Now let me try to insert a null value. Insert into const one values. Single quotes. Single quotes. It fails because of the not null constraint on the column. Now let me insert a numerical value. It works. Verify it. We see the value here. Now let us drop the table just for now.

3. UNIQUE CONSTRAINT

Unique constraint on a column will prevent that column from having duplicate data. It will allow values that are unique. And if we try to insert a value which is already in there, then this constraint will prevent that insertion. But it will allow null values. In fact, it will allow multiple null values if needed. And remember, a null is not equal to another null. The equals to operator doesn’t work on nulls. Okay, let us create a table with unique constraint. Create table cons one. Call one number constraint. Cons one underscore UK unique. Here I am specifying a constraint name called cons one underscore UK. This can be any name.

I am also specifying the type of the constraint by using the keyword unique meaning that it could be an unique constraint. Table created. Now let me insert a null value. It works. Verify. It done. Let me insert the number one. Works fine. Let me insert another row with value one. Again, it fails because there is already a one. And inserting another one will violate the uniqueness enforced by this unique constraint. But let me insert the number two. It works. Verify. What about I insert one more null? I know there is already a null value. It works. Verify. It works. As two nulls are null, not equal to each other. So the unique constraint allows null values, but it preserves the uniqueness among the non null values. Let me drop the table.

4. PRIMARY KEY CONSTRAINT

Primary key constraint enforces the uniqueness of a row in a table. It doesn’t allow any null values. You may think of it as a combination of unique and not null constraints. Let me create a table with a primary key. Constraint constraint create table cons one call one number constraint const one underscore PK primary key here I am specifying a name for the constraint as cons one PK it can be any name. I am also specifying the type of the constraint as primary key. Here, let me run it. Table created now let me try to insert a null value.

It fails as null values are not allowed. Let me insert the number one. It works. Let me verify it. Done. Let me insert one again. It fails as there is already a one in there. So uniqueness is being enforced here. Let me insert two this time. It works. Let me verify it. One and two. So a primary key is a combination of unique constraint and not null constraint. It enforces the uniqueness of the data and it doesn’t allow null values.

5. FOREIGN KEY CONSTRAINTS

A foreign key constraint refers to a primary key constraint. This is needed because there will be situations where a data set must be a subset of another data set. It cannot have any other values. For example, when a company allocates parking spaces to its employees, then it needs to make sure that the parking spaces are allocated to its employees only and not to anyone else, even by mistake. So the parking space allocation has to be a subset of employees data. This can be implemented by using foreign key constraints. From the previous video, we know that the values in const one table are one and two. Now let me create another table called const two.

Create table const Two call eleven number constraint const Two underscore FK foreign key call eleven references const One call One here I am specifying the constraint name as cons to underscore EFK and also the type as foreign key. However, when I do that, I am also specifying the column name. The column name is Call Eleven, which I am just creating as a part of this const two table. I also mentioned that call one one references call one of Const one table. What this means is call one one of Const two table references call one of Const one table.In the database world, this is also called as Parent Child Relationship. Call eleven of Const two table is a child to Call one of Kans One table. Or they may simply say const two table is a child to Const one table.

Let’s query the Cons one table. One and two. This is the parent data set. Remember this again. This is the parent data set, which is one and two. Let us also query the Const two table. No rows in there as expected. Now let me insert number one into cons two. It works because one is part of the parent data set, which is one and two. Verify it. One got inserted. Now let me insert three in const two. Now it fails because three is not part of the parent data set, which is one and two. One and two is the parent data set. Now let me insert two into const two. It works because two is part of the parent data set. Let us verify the data. One and two. This is how a simple primary key and foreign key relationship works.