I am writing a number of scripts to update numerous Access tables. I would like to add a column to each that has a field called "date_created" that is a timestamp of when the record is created. Doing this through the table view is simple, simply set the DefaultValue = now(). However, how would I accomplish this in sql? This is my curren...
The inspiration for this question is a stored proc broke on me because it called another stored proc that inserted data in a table whose schema was completely altered. The question is you have a table in a SQL Server database, and you don't know how it got there. You don't have any DDL triggers in place for custom audit information, and...
Am I correct in understanding that CREATE OR REPLACE basically means "if the object exists, drop it, then create it either way?" If so, what am I doing wrong? This works: CREATE TABLE foo (id NUMBER, title VARCHAR2(4000) DEFAULT 'Default Title') And this doesn't (ORA-00922: missing or invalid option): CREATE OR REPLACE TABLE foo ...
Hi, I'm wondering if it is possible to run multiple DDL statements inside a transaction. I'm specially interested on SQL Server, even though answers with other databases (Oracle, Postgre at least) could also be interesting. I've been doing some "CREATE TABLE" and "CREATE VIEW" for the created table inside a transaction and there seems ...
I understand that when adding a column to a table containing data in SQL server, the column must have a NULL option or a default. Otherwise what would SQL Server pad the new rows with? I am at a loss as to why I can't add a NOT NULL column to an empty table however. I have tried this on two instances of SQL 2008 and one instance of SQL ...
Hi, I look for a way how I can create a ddl for my jpa annotated entities. I prefer a pure java way for this. If possible it would be nice to have generate the drop statements too. ...
I'm using DBVisualizer to extract DDL from an Oracle 10.2 DB. I'm getting odd instances of repeated columns in constraints, or repeated constraints in the generated DDL. At first I chalked it up to a bug in DBVisualizer, but I tried using Apache DDLUtils against the DB and it started throwing errors which investigation revealed to be c...
My database background is with Oracle, so I was surprised to discover that Postgres includes schema changes in transactions - if you begin one, create a table and then rollback, the table goes away. It works for adding and removing columns as well. Obviously this is very nice. We're about to make some changes to the way we deploy schema...
QUESTION How can I create a MySQL table so, that a field's default value is the output of a function. Can it be done without an insert trigger? BACKGROUND: Some entities and the groups they belong to need to be observed on a daily basis. To do that I created the following MySQL table: CREATE TABLE entity ( entity_id VARCHAR(1...
Improvements done nvarchar(5000) -> nvarchar(4000) BUT no nvarchar in PostgreSQL => TEXT memory limits to some variables the syntax slightly changed to more readable dashes to underscores Magnus' improvements I am following my plan for my first database project. I would like to know any weaknesses in the queries and in the relationa...
I am doing my first database project. I would like to know which of the following ways should I use to make SQL queries in DDL. #1 CREATE TABLE employees ( id INTEGER PRIMARY KEY, first_name CHAR(50) NULL, last_name CHAR(75) NOT NULL, dateofbirth DATE NULL ); #2 CREATE TABLE employee...
I would like to know how you can prevent to use of two same tags in a database table. One said me that use two private keys in a table. However, W3Schools -website says that it is impossible. My relational table My logical table The context of tables How can you prevent the use of duplicate tags in a question? ...
This question is based on this thread. CREATE TABLE Answers ( QUESTION_ID integer FOREIGN KEY REFERENCES Questions(USER_ID) PRIMARY KEY CHECK (USER_ID>0), ANSWER nvarchar(4000) NOT NULL AUTO_INCREMENT ...
I am doing my first database project. I would like to know why you should use NOT NULL in the following query ... TITLE nvarchar(60) NOT NULL .. Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) PRIMARY KEY ...
Hi all! We are using NHibernate as our ORM framework. We have a need to persist classes we load at run time. We do that according to metadata they come with, that holds names and types of the data they have. In order to build the tables for them at run time, we use the SchemaExport class from the NHibernate ToolSet API. We wanted to as...
I'm using MySQL (nobody's perfect), version 4.1 and I'm used to define some timestamp columns like that: ALTER TABLE foo ADD creation TIMESTAMP DEFAULT NOW() ; I'd like to do exactly the same thing, but for a DATE field. The reason being I don't need a TIMESTAMP precision and since no functional index exists in MySQL, I cannot access ...
This question is based on this answer. What does the following sentence mean? Finally, don't use mixed case identifiers. Do everything lowercase, so you don't have to quote them. Does it mean that I should change the commands such as CREATE TABLE, USER_ID and NOT NULL to lowercase? - It cannot because it would be against commo...
I am doing my first database project. I would like to know how you can have false as the default value for the following SQL -query ... MODERATOR_REMOVAL boolean NOT NULL ... Context CREATE TABLE Questions ( USER_ID integer FOREIGN KEY REFERENCES User_info(USER_ID) PRIMARY KEY ...
Hello. Is there a standard way to simulate a table creation in a database by using SQL? I don't want the table to be created, just check if it could be created. One way would be to create it and then delete it again. Any other way? ...
In my hibernate application there is annotation driven object: AuditEvent. Its very simple and has no foreign key relationships. I archive old entries in this table by moving them to another table OldAuditEvent, which is a clone of the AuditEvent table. Right now we generate the DDL for the entire application using hbm2ddl (on our a...