In database design, use the CREATE TABLE statement to define table structures and constraints to ensure data integrity. 1. Each table needs to specify a field, data type and primary key, such as user_id INT PRIMARY KEY; 2. Add NOT NULL, UNIQUE, DEFAULT and other constraints to improve data consistency, such as email VARCHAR(255) NOT NULL UNIQUE; 3. Use FOREIGN KEY to establish an association between tables, such as the orders table references the primary key of the users table through user_id.
In database design, a clear structure is the first step. Using SQL's CREATE TABLE statement, you can specify fields, types, and constraints for a table to create a solid data foundation.
Understand basic syntax structure
Each CREATE TABLE statement starts with a keyword, followed by the table name and column definitions in parentheses. Each column requires a name, data type, and optional constraints. For example:
- Column names are usually underlined in lowercase letters (such as
user_id) - Common data types include
INT,VARCHAR(n),DATE,BOOLEAN, etc. - The primary key is specified by
PRIMARY KEYto uniquely identify each row
A typical example is as follows:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255),
created_at DATE
); This statement creates a table called users , contains four fields, and set user_id as the primary key.
Add constraints to improve data integrity
In addition to primary keys, other constraints can be used to ensure the consistency and validity of data. Commonly used are:
-
NOT NULL: Make sure that a column cannot be NULL -
UNIQUE: Make sure that the values ??in the column are unique -
DEFAULT: Provides default values ??for columns -
FOREIGN KEY: Establish an association relationship with other tables
For example, if you want email field not to be empty and unique, you can write it like this:
CREATE TABLE users (
user_id INT PRIMARY KEY,
name VARCHAR(100),
email VARCHAR(255) NOT NULL UNIQUE,
created_at DATE DEFAULT CURRENT_DATE
); Here, the default value for created_at is set to the current date. If the field is not specified when inserting a new record, it will be automatically filled.
Use foreign keys to create connections between tables
In a multi-table system, foreign keys are used to maintain reference integrity between different tables. Assume there is also an orders table, which should be associated with the users table, which can be defined like this:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
user_id INT,
amount DECIMAL(10,2),
FOREIGN KEY (user_id) REFERENCES users(user_id)
); This means that user_id in orders table must exist in user_id of the users table to prevent invalid user order records.
It should be noted that different databases have slightly different support for foreign keys. Some lightweight databases (such as SQLite) may not be enforced although they support syntax.
Basically that's it. After mastering these basic elements, you can use CREATE TABLE to build a database model with reasonable structure and clear relationships.
The above is the detailed content of Defining Database Schemas with SQL CREATE TABLE Statements. For more information, please follow other related articles on the PHP Chinese website!
Hot AI Tools
Undress AI Tool
Undress images for free
Undresser.AI Undress
AI-powered app for creating realistic nude photos
AI Clothes Remover
Online AI tool for removing clothes from photos.
Clothoff.io
AI clothes remover
Video Face Swap
Swap faces in any video effortlessly with our completely free AI face swap tool!
Hot Article
Hot Tools
Notepad++7.3.1
Easy-to-use and free code editor
SublimeText3 Chinese version
Chinese version, very easy to use
Zend Studio 13.0.1
Powerful PHP integrated development environment
Dreamweaver CS6
Visual web development tools
SublimeText3 Mac version
God-level code editing software (SublimeText3)
Defining Database Schemas with SQL CREATE TABLE Statements
Jul 05, 2025 am 01:55 AM
In database design, use the CREATETABLE statement to define table structures and constraints to ensure data integrity. 1. Each table needs to specify the field, data type and primary key, such as user_idINTPRIMARYKEY; 2. Add NOTNULL, UNIQUE, DEFAULT and other constraints to improve data consistency, such as emailVARCHAR(255)NOTNULLUNIQUE; 3. Use FOREIGNKEY to establish the relationship between tables, such as orders table references the primary key of the users table through user_id.
Key Differences Between SQL Functions and Stored Procedures.
Jul 05, 2025 am 01:38 AM
SQLfunctionsandstoredproceduresdifferinpurpose,returnbehavior,callingcontext,andsecurity.1.Functionsreturnasinglevalueortableandareusedforcomputationswithinqueries,whileproceduresperformcomplexoperationsanddatamodifications.2.Functionsmustreturnavalu
Using SQL LAG and LEAD functions for time-series analysis.
Jul 05, 2025 am 01:34 AM
LAG and LEAD in SQL are window functions used to compare the current row with the previous row data. 1. LAG (column, offset, default) is used to obtain the data of the offset line before the current line. The default value is 1. If there is no previous line, the default is returned; 2. LEAD (column, offset, default) is used to obtain the subsequent line. They are often used in time series analysis, such as calculating sales changes, user behavior intervals, etc. For example, obtain the sales of the previous day through LAG (sales, 1, 0) and calculate the difference and growth rate; obtain the next visit time through LEAD (visit_date) and calculate the number of days between them in combination with DATEDIFF;
How to find columns with a specific name in a SQL database?
Jul 07, 2025 am 02:08 AM
To find columns with specific names in SQL databases, it can be achieved through system information schema or the database comes with its own metadata table. 1. Use INFORMATION_SCHEMA.COLUMNS query is suitable for most SQL databases, such as MySQL, PostgreSQL and SQLServer, and matches through SELECTTABLE_NAME, COLUMN_NAME and combined with WHERECOLUMN_NAMELIKE or =; 2. Specific databases can query system tables or views, such as SQLServer uses sys.columns to combine sys.tables for JOIN query, PostgreSQL can be used through inf
How to create a user and grant permissions in SQL
Jul 05, 2025 am 01:51 AM
Create a user using the CREATEUSER command, for example, MySQL: CREATEUSER'new_user'@'host'IDENTIFIEDBY'password'; PostgreSQL: CREATEUSERnew_userWITHPASSWORD'password'; 2. Grant permission to use the GRANT command, such as GRANTSELECTONdatabase_name.TO'new_user'@'host'; 3. Revoke permission to use the REVOKE command, such as REVOKEDELETEONdatabase_name.FROM'new_user
What is the SQL LIKE Operator and How Do I Use It Effectively?
Jul 05, 2025 am 01:18 AM
TheSQLLIKEoperatorisusedforpatternmatchinginSQLqueries,allowingsearchesforspecifiedpatternsincolumns.Ituseswildcardslike'%'forzeroormorecharactersand'_'forasinglecharacter.Here'showtouseiteffectively:1)UseLIKEwithwildcardstofindpatterns,e.g.,'J%'forn
How to backup and restore a SQL database
Jul 06, 2025 am 01:04 AM
Backing up and restoring SQL databases is a key operation to prevent data loss and system failure. 1. Use SSMS to visually back up the database, select complete and differential backup types and set a secure path; 2. Use T-SQL commands to achieve flexible backups, supporting automation and remote execution; 3. Recovering the database can be completed through SSMS or RESTOREDATABASE commands, and use WITHREPLACE and SINGLE_USER modes if necessary; 4. Pay attention to permission configuration, path access, avoid overwriting the production environment and verifying backup integrity. Mastering these methods can effectively ensure data security and business continuity.
When to use SQL subqueries versus joins for data retrieval.
Jul 14, 2025 am 02:29 AM
Whether to use subqueries or connections depends on the specific scenario. 1. When it is necessary to filter data in advance, subqueries are more effective, such as finding today's order customers; 2. When merging large-scale data sets, the connection efficiency is higher, such as obtaining customers and their recent orders; 3. When writing highly readable logic, the subqueries structure is clearer, such as finding hot-selling products; 4. When performing updates or deleting operations that depend on related data, subqueries are the preferred solution, such as deleting users that have not been logged in for a long time.
