Creating a table with a TIMESTAMP column can vary slightly depending on the database management system (DBMS) you are using. Below are examples for some common SQL databases, including MySQL, PostgreSQL, and SQL Server.
MySQL
CREATE TABLE example_table (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);
PostgreSQL
CREATE TABLE example_table (
id SERIAL PRIMARY KEY,
name VARCHAR(100) NOT NULL,
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);
SQL Server
CREATE TABLE example_table (
id INT IDENTITY PRIMARY KEY,
name NVARCHAR(100) NOT NULL,
created_at DATETIME DEFAULT GETDATE()
);
SQLite
CREATE TABLE example_table (
id INTEGER PRIMARY KEY AUTOINCREMENT,
name TEXT NOT NULL,
created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
Explanation:
- id: A primary key for the table, typically representing a unique identifier for each row.
- name: A sample VARCHAR (or NVARCHAR) column for storing string data.
- created_at: The TIMESTAMP (or equivalent) column that will automatically store the current timestamp when a new row is inserted.
To execute any of these SQL statements, you would typically use a database client associated with your DBMS, or an interactive SQL console.
Additional Notes:
- The default value for the timestamp can often be set to the current time upon row insertion, using functions like CURRENT_TIMESTAMP, GETDATE(), or their equivalents.
- Ensure that your DBMS has the correct syntax and object types, as there might be specific variations.
Would you like further assistance or examples with a specific DBMS?