The CREATE TYPE statement in SQL is used to define a new user-defined data type in a database. The syntax and functionality can vary between different database management systems (DBMS), such as PostgreSQL, SQL Server, Oracle, etc. Below, I'll provide general guidance and specific examples for a couple of popular DBMSs.
General Syntax
The general syntax for creating a user-defined data type (UDT) is as follows:
CREATE TYPE type_name AS type_definition;
Where type_name is the name of your new type, and type_definition specifies what that type is (like a composite type, an enumerated type, etc.).
Example in PostgreSQL
In PostgreSQL, you can create a composite type, which is essentially a structured type:
CREATE TYPE person AS (
first_name VARCHAR(50),
last_name VARCHAR(50),
age INT
);
In this example, we created a new composite type called person with three fields: first_name, last_name, and age.
Example in SQL Server
In SQL Server, you can create a user-defined data type using the following syntax. Here’s how to create a new scalar type:
CREATE TYPE PhoneNumber AS VARCHAR(15);
In this case, PhoneNumber is defined as a new type that is essentially a VARCHAR(15).
Creating Table with User-Defined Type
PostgreSQL
You can use the user-defined type in a table as follows:
CREATE TABLE employees (
id SERIAL PRIMARY KEY,
person_details person
);
SQL Server
You can also use your user-defined type in a table:
CREATE TABLE Contacts (
ContactID INT PRIMARY KEY,
PhoneNumber PhoneNumber
);
Conclusion
Using CREATE TYPE allows you to create customized data types that can help enforce data integrity and improve clarity in your database schemas. Make sure to refer to the specific documentation for your chosen DBMS for any additional options or specifics.