Copyright

Data Definition Language (DDL): Definition & Example

An error occurred trying to load this video.

Try refreshing the page, or contact customer support.

Coming up next: Data Manipulation Language (DML): Definition & Example

You're on a roll. Keep up the good work!

Take Quiz Watch Next Lesson
 Replay
Your next lesson will play in 10 seconds
  • 0:00 DDL: Definition
  • 4:09 Other Functions of DDL
  • 5:13 Lesson Summary
Save Save Save

Want to watch this again later?

Log in or sign up to add this lesson to a Custom Course.

Log in or Sign up

Timeline
Autoplay
Autoplay
Speed Speed

Recommended Lessons and Courses for You

Lesson Transcript
Instructor: Martin Gibbs

Martin has 16 years experience in Human Resources Information Systems and has a PhD in Information Technology Management. He is an adjunct professor of computer science and computer programming.

This lesson will cover data definition language (DDL), which is a concept that describes SQL tools used to manipulate database structures. You can create, change, and remove tables, table constraints, and other database objects.

DDL: Definition

Data definition language may sound like it's another programming language, but it's actually a method of categorizing the different types of SQL commands.

Data definition language (DDL) refers to the set of SQL commands that can create and manipulate the structures of a database. DDL statements are used to create, change, and remove objects including indexes, triggers, tables, and views. Common DDL statements include:

  • CREATE (generates a new table)
  • ALTER (alters table)
  • DROP (removes a table from the database)

CREATE

The syntax for creating a table is this:

CREATE TABLE table name (field name data type);

For example this:

CREATE TABLE Artists (artistName, varchar);

Please take note that 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.

ALTER

Ok, say you've created a table, Artists, and forgot 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.

This table example adds the constraint of a primary key that's unique (no repeating values are allowed) to the Artist table. Remember that primary keys will always enforce a unique value.

ALTER TABLE Artists ADD PRIMARY KEY (artist_pk);

It's important to use a consistent naming convention for the primary and foreign keys and constraints. If a field itself is going to be a primary or foreign key, it should contain an identifier (such as artistID). The constraint should be named with pk (primary key) or fk (foreign key) when using DDL commands.

Please remember that there's 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.

The constraint name should be named to specify what it is. For example, 'artist_pk' indicates that the constraint is on the Artist table and it's a primary key. For the foreign key, 'artist_fk' denotes the foreign key.

The field name being flagged as primary or foreign should have an identifier. The primary key for the Artist table could be 'artistID'; this same field could be a foreign key in the Album table as 'albumArtistID'.

Let's set up a foreign key relationship between the Artist and the Album tables. This will add the foreign key of 'albumArtistID' to the Album table to generate the relationship between the two.

ALTER TABLE Album
ADD CONSTRAINT artist_fk FOREIGN KEY (albumArtistID)
REFERENCES Artist (artistID);

We have used the DDL ALTER statement to join the tables Artist and Album on the artistID field from the Artist table, saved into the Album table as albumArtistID. The constraints are 'artist_fk' (foreign key) and 'artist_pk' (the primary key on the Artist table).

DROP

So, to drop or remove a table, use DROP TABLE table name;

If you need to remove a constraint, use the DROP command in combination with the ALTER statement like this:

To unlock this lesson you must be a Study.com Member.
Create your account

Register to view this lesson

Are you a student or a teacher?

Unlock Your Education

See for yourself why 30 million people use Study.com

Become a Study.com member and start learning now.
Become a Member  Back
What teachers are saying about Study.com
Try it risk-free for 30 days

Earning College Credit

Did you know… We have over 200 college courses that prepare you to earn credit by exam that is accepted by over 1,500 colleges and universities. You can test out of the first two years of college and save thousands off your degree. Anyone can earn credit-by-exam regardless of age or education level.

To learn more, visit our Earning Credit Page

Transferring credit to the school of your choice

Not sure what college you want to attend yet? Study.com has thousands of articles about every imaginable degree, area of study and career path that can help you find the school that's right for you.

Create an account to start this course today
Try it risk-free for 30 days!
Create an account
Support