SQL Data Definition Language is about creating the structure of your database. Typically, most people use the user-interface (SQL Developer) to create tables, triggers etc… However, it is very important for you to understand the SQL language which is been generated in the background as it helps you to see how the attributes, data types and relationships interact.
Creating an Entity (table)
Creating an entity is actually very simple once you understand some key principles which I will outline below. First, there is a wrap which encases all the attributes (columns) within the table, this wrap tells the database to create a table called “Bookings” and add the listed attributes.
CREATE TABLE Bookings ( Attribute list here );
The attribute list mirrors the Entity-Relation Diagram (ERD) as we can see in the example below. However, we need to consider additional factors which are not listed on the ERD such as CHECKs. I have used a table because it helps to structure your thinking and syntax.
CREATE TABLE Bookings (
If I now convert the above example into actual SQL code it would look the example below. Note that the “FOREIGN KEY REFERENCES Customer(Cust_ID)” component identifies the table name (Customer) and the required attribute (Cust_ID).
CREATE TABLE Bookings
Booking_ID INT NOT NULL PRIMARY KEY,
Cust_ID INT NOT NULL FOREIGN KEY REFERENCES Customer(Cust_ID),
NumberOfChairs INT CHECK (NumberOfChairs>=1)
Alter an entity
- ALTER CONSTRANT
Delete (Drop) an entity
Deleting tables is very simple but can cost you a lot if you get it wrong! If you simply use the “DROP TABLE Bookings” SQL command you will delete the table and all its data. However, it will still be in your recycle bin so you will need to add the “PURGE” command, like below. If you do not use the “PURGE” command you can recover your entity by using the “FLASHBACK TABLE“.
DROP TABLE Bookings PURGE;
There is an added problem if there are other entities linked to the Booking table so we need to use the “CASCADE CONSTRAINTS“. Need o FINISH