SQL DDL | Data Definition Language (DDL) in SQL - sql - sql tutorial - learn sql




 data definition language
  • Data Definition Language (DDL) is a part of SQL that is used to create, modify, and delete database objects such as table, view, and index.
  • Data Definition Language (DDL) is a standard for commands that define the different structures in a database.

Common DDL statements are:

  • Data Definition Language (DDL) is a part of SQL that is used to create, modify, and delete database objects such as table, view, and index. Data Definition Language (DDL) is a standard for commands that define the different structures in a database.
    • CREATE (generates a new table)
    • ALTER (alters table)
    • DROP (removes a table from the database)

CREATE

  • The syntax for creating a table is CREATE TABLE table name (field name data type)
CREATE TABLE Wikitechy (wikitechyName, varchar);
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

NOTE:

  • The semi-colon is required at the end of the statement! It tells the system to process everything before it.
  • If you leave it out, you may have strange results, or even receive errors.
  • When creating a table, the data types most often used include strings (VARCHAR or CHAR, numbers (NUMBER or INTEGER), and dates (DATE).
  • Each system varies in how to specify the data type.
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

ALTER

  • Say you have created a table Wikitechy and forgotten to add a primary key to the table.
  • Use the DDL command ALTER TABLE to add the constraint.
  • Think of a constraint as a rule-enforcer: It limits what type of data is allowed in the data, to identify a primary or foreign key, or to ensure that there are no duplicates in a given field.
  • The following example adds the constraint of a primary key that is unique (no repeating values allowed) to the Wikitechy table.
ALTER TABLE Artists ADD PRIMARY KEY (wikitechy_pk);
  • It is important to use a consistent naming convention for the primary (or foreign) keys and constraints! If a field itself is going to be a primary or foreign key it should contain an identifier (the author of this lesson uses ID as the identifier, e.g., artistID).
  • The constraint should be named with pk (primary key) or fk (foreign key) when using DDL commands.
  • Note: There is an important distinction between the constraint name and the field name! To keep your sanity, it's a good idea to name them differently, and use a consistent naming convention.
  • Constraint Name: This should be named to specify what it is: Example: ' wikitechy _pk' indicates that the constraint is on the Wikitechy table and it is a primary key. For the foreign key, ' wikitechy _fk' denotes the foreign key.
  • Field Name: The field name being flagged as primary or foreign should have an identifier. The primary key for the Wikitechy table could be ' wikitechyID'; this same field could be a foreign key in the Album table as 'albumWikitechyID'.
  • Let's setup a foreign key relationship between the Wikitechy and the Album table. This will add the foreign key of 'albumWikitechy ID' to the Album table to generate the relationship between the two.
ALTER TABLE Album
ADD CONSTRAINT wikitechy_fk FOREIGN KEY (albumWikitechy ID)	
REFERENCES Artist (wikitechyID);
  • We have used the DDL ALTER statement to join the tables Wikitechy and Album on the wikitechyID field from the Artist table, saved into the Album table as albumWikitechy ID. The constraints are ' wikitechy_fk' (foreign key) and ' wikitechy_pk' (the primary key on the Wikitechy table).
Tags : sql tutorial , pl sql tutorial , mysql tutorial , oracle tutorial , learn sql , sql server tutorial

DROP

  • To drop/remove a table, use DROP TABLE table name;
ALTER TABLE Wikitechy
DROP CONSTRAINT WikitechyID;
  • It's not always a good idea to remove a primary key, the field that uniquely identities the records in the table. However, if you want to drop a table that has foreign keys to other tables, they need to be dropped before dropping the table. The important thing here is that you can use the DROP statement to remove constraints from a table.

This tutorial provides more the basic needs and informations on sql tutorial , pl sql tutorial , mysql tutorial , sql server , sqlcode , sql queries , sql , sql formatter , sql join , w3schools sql , oracle tutorial , mysql , pl sql , learn sql , sql tutorial for beginners , sql server tutorial , sql query tutorial , oracle sql tutorial , t sql tutorial , ms sql tutorial , database tutorial , sql tutorial point , oracle pl sql tutorial , oracle database tutorial , oracle tutorial for beginners , ms sql server tutorial , sql tutorial pdf

Related Searches to Data Definition Language (DDL) in SQL