In this video we are going to be creating foreign keys. I highly recommend watching the previous video before you watch this one.
Essentially, we are creating a very simple database for a system where we can create projects and add people to those projects.
We started with the users table:
--Delete the table if needed:
--DROP TABLE users;
CREATE TABLE users(
user_id NUMBER,
username VARCHAR2(50 CHAR) UNIQUE,
CONSTRAINT users_pk PRIMARY KEY (user_id)
);
Now we are going to create a table for projects with a column that is a foreign key to the username. We're going to want to make this match data types:
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR)
)
Next, we need to add the column attributes we decided on last video:
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL
)
NOT NULL because we want every project to have a creator, but we are not labeling UNIQUE because that means we could only have a specific username once in the table. We want to allow a user to create multiple projects.
We also need to add a primary key:
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL,
CONSTRAINT projects_pk PRIMARY KEY (username)
)
Now, the question that remains is, how can I tell Oracle that I want the username to reference the username column of the other table? We have to make a foreign key constraint. As you've learned from the previous videos, there are about three ways to create constraints. You can do it inline at the column level, unnamed. You can do it at the column level, named, and you can do it at the Table level, named. Usually the table-level is preferred, but I will quickly remind you how to do all three again.
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL REFERENCES users (username),
CONSTRAINT projects_pk PRIMARY KEY (project_id)
)
This works, but if we want to name it, we should do this:
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL CONSTRAINT projects_users_fk REFERENCES users (username),
CONSTRAINT projects_pk PRIMARY KEY (project_id)
)
This works, but the preferred method is to do it at the table level:
CREATE TABLE projects(
project_id NUMBER,
project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL,
CONSTRAINT projects_pk PRIMARY KEY (project_id),
CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username)
)
Great! So you've learned how to create a foreign key, now we can see it inside of Oracle SQL Developer.
One important thing when it comes to foreign keys is what happens when have data in your database and you try to delete the parent row that a row in the child table references? In the next video we are going to configure that using ON DELETE. See you all then and if you enjoy this series, please do me a huge favor by liking the video and subscribing to my YouTube channel.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HELP 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!)

SQL Tutorial 18: SQL foreign key constraint.
Topic covered in this tutorial
1. Defining SQL foreign key using create table at column level
2. Defining SQL foreign key using create table at table level
1. Defining SQL foreign key using Alter Table statement
Links
Website article: http://www.rebellionrider.com/sql-foreign-key.htm
Primary key tutorial: http://www.rebellionrider.com/sql-foreign-key.htm
Create Table Tutorial: http://www.rebellionrider.com/sql-create-table.html
Tool used in this tutorial is SQL Developer.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=j7eYO7
Contacts
E-Mail
[email protected]
Twitter
https://twitter.com/rebellionrider
Instagram
http://instagram.com/rebellionrider
Facebook.
https://www.facebook.com/imthebhardwaj
Linkedin.
http://in.linkedin.com/in/mannbhardwaj/
Thanks for linking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
Foreign key is an Input/output data constraint which is also known as referential integrity constraint.
Foreign key represent a link or say a relationship between columns of tables.
Similar to primary key constraint Foreign Key constraint is also of two types.
Simple Foreign key constraint and
Composite Foreign key constraint.
Constraint which involves only one column in foreign key in child table and one column in reference key in parent table is called Simple Foreign Key. While the constraint which involves more than one column in foreign key in child table and more than one column in reference key in the parent table is called Composite Foreign Key.

Blog link: http://www.rebellionrider.com/sql-primary-key.htm
SQL tutorial on Primary key / SQL Primary key,
In this tutorial you will learn about
simple primary key, composute primary key, how to drop primary key. defining primary key using create table and alter table along with many other things.
Tool used in this tutorial is SQL Developer.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=j7eYO7
Contacts
E-Mail
[email protected]
Twitter
https://twitter.com/rebellionrider
Instagram
http://instagram.com/rebellionrider
Facebook.
https://www.facebook.com/imthebhardwaj
Linkedin.
http://in.linkedin.com/in/mannbhardwaj/
Thanks for linking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com
So Primary Key
There are two types of Primary keys
First is Simple primary Key: Primary key which Involves only one column and
Second is Composite Primary Key: Primary Key which involves more than one column.
We will start with Simple primary key.
You can create Primary key either with Create Table statement of by using Alter table Statement.
Let's define simple primary key using create table statements.
With create table statement we can either define primary key at column level or at table level.
We will start with defining Primary key at column level using create table statement.
You just have to put keyword Primary key after data type and size of column while defining a column of a table. Here oracle server will create a primary key on product id with default constraint name which will be slightly difficult to understand. You can give your own name to your constraint which is also a good practice.

SQL Server / T-SQL Tutorial Scenario:
You are working as SQL Server developer, you are asked to provide the scripts those can be used to drop Foreign Key Constraints on all the tables in a database if exists.
Link to scripts used in SQL Server / TSQL Tutorial Video
http://www.techbrothersit.com/2016/04/how-to-drop-foreign-key-constraints-in.html
Check out our website for Different SQL Server, MSBI tutorials and interview questions
such as SQL Server Reporting Services(SSRS) Tutorial
SQL Server Integration Services(SSIS) Tutorial
SQL Server DBA Tutorial
SQL Server / TSQL Tutorial ( Beginner to Advance)
http://www.techbrothersit.com/

In the last video, we wrote up the SQL to create three tables:
CREATE TABLE users(
user_id NUMBER,
username VARCHAR2(50 CHAR) UNIQUE,
CONSTRAINT users_pk PRIMARY KEY (user_id)
);
CREATE TABLE projects(
project_id NUMBER,
Project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL,
CONSTRAINT projects_pk PRIMARY KEY (project_id),
CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username)
ON DELETE CASCADE
);
CREATE TABLE project_users(
project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE,
user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
CONSTRAINT project_users_pk PRIMARY KEY (project_id, user_id)
);
I'm going to increase the size of the users table a bit by adding a first and last name column.
CREATE TABLE users(
user_id NUMBER,
username VARCHAR2(50 CHAR) UNIQUE,
first_name VARCHAR2(50 CHAR),
last_name VARCHAR2 (50 CHAR),
CONSTRAINT users_pk PRIMARY KEY (user_id)
);
But before we finish this design, we should consider indexing certain columns. What columns should we index? Well, as a reminder, the columns that are indexed by default are columns with the UNIQUE constraint, and those that are labeled as primary keys. Columns that are not indexed but often should be are those labeled as a foreign key. The column that jumps out the most to me is the creator column of the project table. It's the only foreign key that is not part of some index. Let's fix this by creating our first index. The way we do that is with the CREATE INDEX command.
CREATE INDEX projects_creator_ix
ON projects (creator)
What naming convention are we following for the index? We are naming it by the table name, followed by an underscore, followed by the column, followed by an underscore, followed by an ix (for index).
In this situation it does not apply, but if our foreign key column is labeled as UNIQUE, we can add the UNIQUE keyword like this:
CREATE UNIQUE INDEX projects_creator_ix
ON projects (creator)
Now if you want to get rid of an INDEX, you can use this command:
DROP INDEX projects_creator_ix
Now, if we want to select data from the user table and the project table we can do that much faster. That's because the foreign key and column it references are both indexed and those are the columns we would do the join on. We will discuss how to do joins in a future video. So what are some potential problems with this database design? Overall, it is pretty good. With this design though we need to make sure there is no way for someone to update a user's username. In the next video we are going to discuss why.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Support me on Patreon! 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!)

SQL Server / T-SQL Tutorial Scenario:
You are working as SQL Server DBA or Developer, You need to drop a table from a database. When you execute drop table SchemaName.TableName statement, you get below error.
Msg 3726, Level 16, State 1, Line 12
Could not drop object 'SchemaName.TableName' because it is referenced by a FOREIGN KEY constraint.
Now we know that the table is referenced by Foreign Key Constraint. The problem is how to find which table has that Foreign Key Constraint that is referencing to this table.
Link to scripts used in SQL Server / TSQL Tutorial Video
http://www.techbrothersit.com/2016/04/could-not-drop-object-because-it-is.html
Check out our website for Different SQL Server, MSBI tutorials and interview questions
such as SQL Server Reporting Services(SSRS) Tutorial
SQL Server Integration Services(SSIS) Tutorial
SQL Server DBA Tutorial
SQL Server / TSQL Tutorial ( Beginner to Advance)
http://www.techbrothersit.com/

HAPPY VALENTINES DAY!!! Let's talk relationships..
This is the 6th video in your Oracle Database series. We are discussing database relationships which are a key feature of relational database management systems. We first discussed entities and attributes. I talked about how each entity is assigned a table and each attribute is a column within a table.
We moved on to the three kinds of relationships. The first was one-to-one. This describes an entity and an attribute. A piece of data that is exclusive to an entity is, by definition, an attribute of that entity. This is stored in one table with the attribute being a column within this table.
The second relationship is one-to-many. This relationship is between two entities. The way we properly store this in a database is using a foreign key in the child table. Remember, the child table is the entity on the many side of the one-to-many relationship. Every row within the child table will have a value for the foreign key that references a primary key in the parent table. This assumes that the foreign key field is not optional (NOT NULL). If the foreign key is optional, than a reference is not required but any reference must be valid.
The third kind of relationship is a many-to-many relationship. In this situation, we need 3 tables. The many-to-many relationship is broken up into two one-to-many relationships. The intermediary table will associate each entity from one table with the appropriate entities in the other table. There is debate as to whether this table needs a primary key. This is because you can intact use the combination of two foreign keys as a primary key. This works because we will never have two duplicate rows within the intermediary table and the two foreign keys work as a compound key because of this.
We finally discussed primary keys and foreign keys. Primary keys are used to keep each row inside of a table unique. If this key is a computer generated number it is known as a surrogate key, otherwise it is known as a natural key. Natural keys have real world meaning. For example, a social security number may work, or an email address (in some situations), etc. Whichever type of key you choose is solely up to you and/or the company you may be working for.
HELP 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!)

How to drop all of the objects in a user's recycling bin
==========================================================
purge recyclebin
how to check the original name of dropped objects in recyclebin
===============================================================
select owner,original_name,type from dba_recyclebin;
select original_name,type from recyclebin;
how to check view on recyclebin
===================================================
select table_name from dict where table_name like '%RECYCLE%';

In this lecture our topic is Foreign Key, Drop Foreign Key in SQL & Cascade Vs Restrict Property.
This course is first (fundamental to advance) course on database management systems and structured query language. Complete SQL implementation with XAMPP. Advance projects are also included in this course. The course discusses different topics of the databases. We will be covering both the theoretical and practical aspects of databases.
Our website: http://www.exceptionalprogrammers.com
Facebook: https://www.facebook.com/ExceptionalProgrammers/
G mail: [email protected]

Welcome everyone! Something you need to consider when you are creating foreign keys is what happens if you delete the parent? As a reminder, the parent is the row that has the value you are referencing in the row that has a foreign key.
Why is this something important to consider? It's important because foreign keys need to protect us from two primary things, unacceptable INSERT statements, and unacceptable DELETE statements.
Let's see what happens when we try to insert incorrect data into the table with the foreign key:
INSERT INTO projects
VALUES (1, 'Update website homepage', 'CalebCurry')
The response tells us plainly that there is no such user in the users table. So this works correctly. Deleting data on the other hand works a bit differently because the database does not know what you want to do with the child row when you delete the parent from the parent table. By default, we will get an error message that prevents the parent from being deleted, but there are some other options. How do we configure this?
This is where the ON DELETE statement comes in.
We add the keywords ON DELETE right after the foreign key and then we can give it the option of CASCADE or SET NULL.
CASCADE means that if we delete the parent, we are also going to delete the child. In our situation what that means is that if somebody creates a project in our project table and then that persons account gets deleted, all of the projects he owns will also be deleted.
CASCADE:
CREATE TABLE projects(
project_id NUMBER,
Project_name VARCHAR2(50 CHAR) UNIQUE,
creator VARCHAR2(50 CHAR) NOT NULL,
CONSTRAINT projects_pk PRIMARY KEY (project_id),
CONSTRAINT projects_users_fk FOREIGN KEY (creator) REFERENCES users (username)
ON DELETE CASCADE
)
SET NULL will take the value in the child table and get rid of it. What you are left with is NULL. This means that we have an orphaned child. The first thought you might have is that it is a bad thing to have an orphaned child, but in databases that is not always so. In our application if we had it set to SET NULL, when a user account gets deleted the projects would remain in existence they would just lack a creator. This might be a good thing if you are concerned about the long term survival of a project, this might be the route you want to go. It ultimately depends on the application purpose. If you don't like CASCADE or SET NULL, you can leave the entire ON DELETE statement and just have Oracle throw an error when a parent is deleted.
As for us, we are going to use ON DELETE CASCADE. We need to use this with extreme caution. If you are not careful, someday you will run a delete a row and that will cascade through you database deleting a bunch of stuff you didn't want to delete. Stuff happens, so make sure you back up your database every once in eternity.
Now, in the last video we started with a database design that had three tables. We've only created two in this video. In the next video we are going to create the next one, which is a little special. Then we'll finish things up by adding some indexes.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Support me on Patreon! 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!)

We are going to continue working with the users table that we've started with, but we are going to add a few tables. Imagine a system where you can create projects. And users can be added to these projects. So this could be some kind of productivity app or a project management solution, think of JIRA.
We are going to start with three tables. The first table is going to be a users table that contains all of the information about each user's account.
We are then going to have a table that is called projects. Each project will have data about the project and a foreign key that is the creator of the project. This is a situation where the database design depends a lot on the business rules and requirements of the application. Is it appropriate to have only one creator, or can it have multiple creators? We are going to design it with only one creator per project to increase simplicity.
The third table is going to be used to record what users are part of certain projects. This situation is a many to many relationship because we've decided that one user can be a part of multiple projects and an individual project can have multiple members working on it. Because this is a many to many relationship, it calls for an intermediary table, project_users.
First, we will draw out the user table. We will have a user_id, username, first_name, and last_name.
Now, this is our parent table, because it has no foreign keys. Now, this is our parent table, because it has no foreign keys. Other tables are going to be referencing this table, so they would be the children.
The project table will have a project_id, title, description, and creator. The column that needs to be a foreign key is the creator.
Let's move on to the next table and we'll get back to the foreign key of the project table. The other table was project_users. Knowing that this is an intermediary table, immediately we know that the first two columns are going to be foreign keys to the each of the other tables.
Now, let's ask the important questions about the foreign keys. Let's first start with the project table's user column. The first thing we need to ask is what column does it need to reference? Remember, the only options are the columns that are UNIQUE. Our candidates are user_id and username. For now, let's go with username as it makes things easier to work with. Once we go into learning about joins, we will talk about joining things by ID. Different people do it different ways, with the majority using only ID columns for primary and foreign keys, but it's important to be familiar with different ways of doing things. The important thing to remember is that keys should never change, so if we should only reference the username if a user's username will never change.
Should the foreign key be labeled UNIQUE? If yes, it means that a user can only create one project. I vote no.
Should the foreign key be labeled NOT NULL? If not, it means that a project can exist without a creator. I vote no.
Moving on to the next table, I think I'll have the columns reference the project's id and user's id, so we can get some experience referencing surrogate keys.
We can apply to these foreign keys the same questions we asked about the other foreign key, and I would encourage you to do so and really think about why. But I can tell you that we are not going to want them to be NOT NULL, but not UNIQUE.
Now that we have a pretty decent database design, we can proceed with creating our database.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HELP 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!)

This video is going to be a tutorial on how to create composite and compound keys. The difference between a composite and compound key is that a composite key can consist of any columns while a compound key has to consist of columns that are all keys themselves. We will be working with a compound key because we are going to be using the an intermediary table that has two foreign keys. The combination of both of the keys have to be unique.
First, if we have any other CREATE TABLE commands, we are going to comment those out. We will space out the CREATE TABLE to have each column on a line, then we will add constraints as needed.
CREATE TABLE project_users(
project_id,
user_id
)
Now, let's add the data types:
CREATE TABLE project_users(
project_id NUMBER,
user_id NUMBER
}
Now, what about some column attributes? I'm going to make both of the columns NOT NULL because we always want the rows to have a user and a project:
CREATE TABLE project_users(
project_id NUMBER NOT NULL,
user_id NUMBER NOT NULL
}
Now, let's add the foreign key constraints. Now, what do we name these? We are going to add a primary key that covers both of these columns, so I'm going to be a sinner and not give these constraints names:
CREATE TABLE project_users(
project_id NUMBER NOT NULL REFERENCES projects (project_id),
user_id NUMBER NOT NULL REFFERENCES users (user_id)
)
Now, the way we have it now is that if we delete a project in the project table, and there are any rows in the project_users table, it will throw an error and prevent deletion. I would prefer for it to also delete any project members. That would make sense because if you delete a project we want it to delete the associate between that project and certain users. The same goes for if we delete a user, we want their association with a certain project to be deleted. To do this, we need to add the ON DELETE command:
CREATE TABLE project_users(
project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE,
user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
)
Finally, let's learn how to create a compound or composite key. literally, the only difference is that you put a comma and add the second table inside of the parenthesis.
CREATE TABLE project_users(
project_id NUMBER NOT NULL REFERENCES projects (project_id) ON DELETE CASCADE,
user_id NUMBER NOT NULL REFERENCES users (user_id) ON DELETE CASCADE,
CONSTRAINT project_users_pk PRIMARY KEY (project_id, user_id)
)
Now, the combination of project_id and user_id cannot be null, is always unique, and has an index.
The only thing we should do now is add a few indexes. We aren't done yet...In the next video we are going to figure out what columns would benefit from indexes and we'll add them to it.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
HELP 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!)

Subscribe to IT PORT : https://www.youtube.com/c/itport28
SQL Server Tutorial in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ64enJDwWVvN3KduuZRlUrb3
SQL Server Concepts in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ67kdNIByJKAGBIBhor_h4Hs
SQL Server Analytic Functions in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ65XIU5azEUgLVrBKEl-jWMV
SQL Server Tutorial : https://www.youtube.com/playlist?list=PLouAaeZ4xQ66AYrzPtxt2SeeR4UABcBcO
SQL Server Concepts : https://www.youtube.com/playlist?list=PLouAaeZ4xQ66zRe8-nDDy-YHY2o0rmbn4
SQL Server Analytic Functions : https://www.youtube.com/playlist?list=PLouAaeZ4xQ64hTsEdhLsVBquE1vERWngX
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table. A table can have 253 foreign key constraints.
Explained in Tamil

Subscribe to IT PORT : https://www.youtube.com/c/itport28
SQL Server Tutorial in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ64enJDwWVvN3KduuZRlUrb3
SQL Server Concepts in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ67kdNIByJKAGBIBhor_h4Hs
SQL Server Analytic Functions in Tamil : https://www.youtube.com/playlist?list=PLouAaeZ4xQ65XIU5azEUgLVrBKEl-jWMV
SQL Server Tutorial : https://www.youtube.com/playlist?list=PLouAaeZ4xQ66AYrzPtxt2SeeR4UABcBcO
SQL Server Concepts : https://www.youtube.com/playlist?list=PLouAaeZ4xQ66zRe8-nDDy-YHY2o0rmbn4
SQL Server Analytic Functions : https://www.youtube.com/playlist?list=PLouAaeZ4xQ64hTsEdhLsVBquE1vERWngX
A foreign key (FK) is a column or combination of columns that is used to establish and enforce a link between the data in two tables. You can create a foreign key by defining a FOREIGN KEY constraint when you create or modify a table.
In a foreign key reference, a link is created between two tables when the column or columns that hold the primary key value for one table are referenced by the column or columns in another table. This column becomes a foreign key in the second table. A table can have 253 foreign key constraints.

The goal of this video is to take the CREATE TABLE statement we have and walk through the different ways to create primary keys. This and foreign keys are the most common constraints, so we need to make sure that you have this one down. Once we have a more complex database design with multiple tables, we will learn the proper way to create foreign keys.
For now, I am going to keep all of our constraints at the column level, unnamed. The only exception is the primary key, because that is what we are focusing on in this video. The first way to create the primary key is at the column level, unnamed. The primary key is very important because it what we use to distinguish rows from one another. Every table you create is going to need a primary key, and I suggest putting a lot of effort into making sure your keys are set up correctly and organized.
--Delete the table if needed:
DROP TABLE users;
CREATE TABLE users(
user_id NUMBER PRIMARY KEY,
username VARCHAR2(50 CHAR) NOT NULL UNIQUE
)
The next way is at the column level, but named:
--Delete the table:
DROP TABLE users;
CREATE TABLE users(
user_id NUMBER CONSTRAINT users_pk PRIMARY KEY,
username VARCHAR2(50 CHAR)NOT NULL UNIQUE
)
The general naming convention here is the table name followed by an underscore, followed by pk for primary key.
Finally, the third way, which is at the table level, is the way we are going to create our primary key:
--Delete the table:
DROP TABLE users;
CREATE TABLE users(
user_id NUMBER,
username VARCHAR2(50 CHAR) NOT NULL UNIQUE,
CONSTRAINT users_pk PRIMARY KEY (user_id)
)
Now, once you've created these constraints, you can use Oracle SQL Developer to find these constraints. Open your databases in the connections tab and find the table in the Tables folder. Double click your table and travel to the Constraints tab. You can tell here that the UNIQUE constraint still exists in this table, but it has a pretty disgusting name. It kind of wants to make me puke. Referencing that constraint in the future with that wacky name would be a burden.
Engrave these three options in your head so that you can use any of them whenever you need and can read other peoples' code.
Thank you for sticking with the series thus far. In the next video, we will be…doing something. See you then! :)
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Support me on Patreon! 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!)

Oracle Database 11g Tutorial 20: On delete cascade
This tutorial will show you how to define foreign key with on delete cascade clause.
Links
Blog: http://www.rebellionrider.com/on-delete-cascade.htm
SQL Tutorial 19 On delete set Null: http://www.rebellionrider.com/on-delete-cascade.htm
SQL Tutorial 19 Foreign Key:
http://youtu.be/0nbkBI5r3Gw
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=j7eYO7
Tool used in this tutorial is SQL Developer.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Contacts
E-Mail
[email protected]
Twitter
https://twitter.com/rebellionrider
Instagram
http://instagram.com/rebellionrider
Facebook.
https://www.facebook.com/imthebhardwaj
Linkedin.
http://in.linkedin.com/in/mannbhardwaj/
Thanks for liking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com

Let's think about a database that we use for our ETL process and we call that database staging. We want to truncate all the tables in morning so those can be used for the daily process. Once way is to write our Truncate statement for each of the table but tables can be dropped or new tables can be created as well. We want to write a query that we can be use to Truncate all the tables from a database and we don't have to rewrite our Truncate statements.
If the tables do not have Primary Key -Foreign Key relationship, we can use Truncate. But we often have tables which are referenced by other tables. We can not use Truncate statement for those tables. The only way to Truncate those tables is to drop the Foreign Key Constraint before Truncating and then Truncate and Recreate Relationship after that.
The below Code can be used to Truncate all the tables from the database. If Tables will have the Pk-Fk relationship, The script will drop the Foreign Key Constraints first and then Truncate those tables and finally recreate the Foreign Key constraints.
Before you run below Script, Make sure you are pointing to correct Database and Server! All the best with Truncating all the tables from a SQL Server Database.
Link to Blog for Script used in this video
http://sqlage.blogspot.com/2015/02/truncate-all-tables-in-database-in-sql.html
visit our website for more technology tutorials
www.techbrothersit.com

Creating indexes is a very easy task, but before you go creating indexes on everything, you need to know that some columns are indexed by default. Specifically, any column that is labeled as the PRIMARY KEY or UNIQUE are indexed by default. That means that if you have a UNIQUE column, you do not need to worry about adding an INDEX to it.
The columns that you will want to pay special attention to are any foreign keys or columns that you are going to use very frequently.
We are going to create an index on our SpeciesID column in our Animals table. This is a foreign key that I'm likely going to use very frequently.
CREATE INDEX IX_AnimalsSpecies
ON Animals (SpeciesID);
The syntax is very similar to CREATE TABLE in that you say CREATE INDEX followed by an index name. The IX_ is a prefix that is sometimes conventionally used to name indexes. Next, we have the table name, and then we have something to describe the column. That way if you see IX_AnimalsSpecies, you automatically know it is talking about an Index for the Animals table that is on the column dealing with the species.
To get rid of this index, use this:
DROP INDEX IX_AnimalsSpecies;
Which is also very similar to how we drop a table.
Now, you can also create an INDEX on multiple columns if you are going to use them together very often. The way you do that is just add another column after a comma inside of the () in the index columns. For example:
CREATE INDEX IX_AnmialsContact
ON Animals (Name, ContactEmail);
One other thing I wanted to show you is that you can actually create a UNIQUE Index by adding the keyword in:
CREATE UNIQUE INDEX IX_Species
ON Species (Species);
This should only be used if every value for a column is labeled unique…In fact, it forces every row to be unique. Remember though that if a column is labeled UNIQUE, it is indexed by default...So this is not needed in this situation. In the next video we will discuss in more detail whether you want to use the UNIQUE Constraint create a UNIQUE index.
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
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!)

Below is link for Useful Pl/SQL Books
http://goo.gl/XMy0tt
----------------------------------------------------------
Difference Between Delete and Truncate in Detail
On bigger picture they serve the same purpose but there are many Differences listed with examples
Point Delete Truncate
1. Data Recovery
Delete: Come under the DML Category, we need to commit or Rollback explicitly to make the changes permanent, so we can recover the data by Rollback command fully with in a session or up to a point if Save Points are used
Fall In DDL Category (DDL Command issue the Auto commit implicitly) so no chances of Recovery even not using the Flashback table method.
But Truncate operations are also logged , they didn’t generate redo SQL but they are logged , view for truncated data info V$LOGMNR_CONTENTS
2. Data Removal
Delete Can remove all or selected data using the Where Clause predicates. Or we can say delete any subset of rows
We can Truncate complete table or a partition or sub partition of a table.
3. Speed
Delete is Slower because oracle maintain the redo logs for Read Consistency (so that every session connected can see a consistent data at a given point of time ) Delete is very time consuming activity especially when table have numerous indexes and Triggers associated with table
Faster as no data logs are maintained no associated trigger firing.
4. DML Triggers Firing
DML (Delete) triggers associated with table will fire.
DML Trigger will not fire in case of truncate method.
5. Flashback Technology
Data can be recovered even after commit operation using Flashback Table options Flashback_transaction_query table will give what to recover and up to which point.
Data cannot be recovered in truncate method by Flashback table option.
6. Referential Integrity Constraint Behavior
if we don’t have related data in child table then we can delete the data from the parent table or we have variants like On Delete Cascade & on Delete set Null.
We can’t truncate a table with enable Referential Integrity Constraint, even there is no data in the child table, we have to disable or drop the constraint if we want to truncate the table.
Exception: Truncate is possible if the FK is self-referential means primary key and foreign key are on the same table.
7. Space De allocation or Space Utilization
No extent reset with delete when deleting rows from a table, extents are not de allocated,
So if there were 50 extents in the table before the deletion, there will still be 50 after the deletion.
Truncate: When a table is truncated it will free the space allocated except in case of reuse storage clause. This space can subsequently be used only by new data in the table or cluster resulting from insert or update operations .All extents are de allocated leaving only the extents specified when the table was originally created .Example So if the table was originally created with min extents 3, there will be 3 extents remaining when the tables is truncated. When you truncate a table, NEXT is automatically reset to the last extent deleted.
8. High Water Mark
Delete will not reset the high water mark
Truncate will reset the High Water mark which is very important for performance point of view as in case of full table scan and full index scan oracle will read all the block under high water mark this makes a lot of difference in terms of performance.
9. Cluster
No as such restriction with delete.
You cannot individually truncate a table that is part of a cluster. You must truncate the cluster, Delete all rows from the table, or drop and re-create the table.
10. Information Capturing
Delete : we can capture the row information what we have deleted using Delete Method, f you are deleting multiple records then use composite data types (collections & records)
Truncate Don’t have this feature of capturing the deleted records.
11. Function Based Index Impact
DELETE You cannot delete rows from a table if a function-based index on the table has become invalid. You must first validate the function-based index.
Truncate: No as such restriction
12. UNUSABLE Indexes
Delete no as such feature.
Truncate if table is not empty then truncate make all unusable indexes to useable.
13. Complex views
You cannot delete data from a Complex view except through INSTEAD OF triggers.
But we can delete data from simple Views and MV.
We cannot truncate a view simple or complex but you can truncate MV with special Features like Preserve MV Logs and Purge MV Logs.
14. Privileges
Delete You need to provide delete table privilege on object.
Truncate you must have drop table privilege there is no truncate table privilege exists.
15. Domain Index
No as such restriction
You cannot truncate the object having domain index in invalid or In progress state

SQL Tutorial 19 : ON DELETE SET NULL foreign key Clause.
Links used in the tutorial
On delete set null website article [ download SQL script from same link] :
http://www.rebellionrider.com/on-delete-set-null.htm
Foreign Key Tutorial Video : http://youtu.be/0nbkBI5r3Gw
Foreign Key Tutorial Blog : http://www.rebellionrider.com/sql-foreign-key.htm
This video is the part of foreign key video In this sql tutorial you will learn the referential clause ON DELETE SET NULL of foreign key.
On Delete Set null clause guarantee the relation integrity in foreign key/
Tool used in this tutorial is SQL Developer.
This tutorial series is part of SQL expert exam certification training. if you are preparing for SQL certification you can use my tutorials. This SQL Tutorial is a part of free training.
Copy Cloud referral link || Use this link to join copy cloud and get 20GB of free storage
https://copy.com?r=j7eYO7
Contacts
E-Mail
[email protected]
Twitter
https://twitter.com/rebellionrider
Instagram
http://instagram.com/rebellionrider
Facebook.
https://www.facebook.com/imthebhardwaj
Linkedin.
http://in.linkedin.com/in/mannbhardwaj/
Thanks for liking, commenting, sharing and watching more of our videos
This is Manish from RebellionRider.com