As Martin Smith suggested in the comments, a trigger probably isn't the best approach here. Instead, you could use a computed column, which is, in a nutshell, a column that has its value calculated ("computed") by some other columns in the table. In your case: CREATE TABLE my_table ( col1...
sql,postgresql,ddl,alter-table,table-rename
No. While other actions can be combined, that's not possible with RENAME. As per documentation: All the actions except RENAME and SET SCHEMA can be combined into a list of multiple alterations to apply in parallel. Since RENAME is a tiny operation on a system catalog, there is no harm...
sql,oracle,sqlplus,ddl,database-metadata
Check the function: DBMS_METADATA.GET_DDL http://psoug.org/reference/dbms_metadata.html...
oracle,oracle11g,oracle10g,ddl
DDL statements in Oracle are each a transaction. Each DDL statement causes few or many changes in the data dictionary, like obj$. I am not sure, but looking at the major work Oracle has gone through to ensure that locking is not an issue with even the early versions of...
Just create a new user and give him the connect role and just select permission on the tables, views he is allowed to see. create user test identified by notagoodpassword; grant connect to test; grant select on schema.table to test; Edit: if you want the user to call a procedute/function...
Yes, you should create the foreign key constrain on both tables. The foreign key constraints are there to maintain referential integrity; ensuring that you can't insert values that don't exist in the parent table. If you don't add the constraint to HOUSE_USER_GROUPE_ACCESR then you don't get that protection in that...
Erwin's answer is correct, I just want to address another aspect. I am trying to store number of seconds since 1970 in the column Please don't do that. It's annoying to query and is of no storage benefit over just using a timestamp. Store timestamp without time zone. Or, preferably,...
mysql,sql,stored-procedures,ddl
Do you just want group_concat()? select event_a, group_concat(event_b) as event_bs from table t group by event_a; ...
Column C2 current definition: C2 date default sysdate When you're altering this column to VARCHAR2(10) there is no problem, because Oracle performs an implicit conversion of the values from DATE to VARCHAR2 using current NLS_SETTINGS (date string representation pattern for example) When you're altering the column to NUMBER(9) Oracle cannot...
sql,sql-server,foreign-keys,constraints,ddl
The last line CONSTRAINT FK_CLIENTS FOREIGN KEY (clients_id) REFERENCES clients(id) Is equivalent to the iniline definition following client_ids: FOREIGN KEY REFERENCES clients(id) One of the two should be removed....
Try this without creating any transaction: CREATE TABLE ClientPayerCrosswalkMapping ( id INT IDENTITY(1, 1) NOT NULL, lbxid INT NOT NULL, ClientPayerID NVARCHAR(80), CONSTRAINT PK_ClientPayerCrosswalkMapping_ID PRIMARY KEY CLUSTERED (id), CONSTRAINT UN_lbxid UNIQUE(lbxid), CONSTRAINT FK_LockboxDocumentTracking FOREIGN KEY (lbxid) REFERENCES LockboxDocumentTracking(lbxid) ); ...
database,postgresql,ddl,materialized-views
Show all: SELECT oid::regclass::text FROM pg_class WHERE relkind = 'm'; Names are automatically escaped and schema-qualified according to your current search_path where necessary in the cast from regclass to text. Materialized views in the system catalog pg_class are identified by relkind = 'm'. Per documentation: m = materialized view To...
sql,postgresql,triggers,updates,ddl
Since a in your scenario should always be 18 months after z there's no point in actually storing it anywhere. It would be much easier to just have it as a calculated column in a view: CREATE VIEW y_view AS SELECT z, z + INTERVAL '18 MONTH' AS a FROM...
Sql Server Management Objects http://msdn.microsoft.com/en-us/library/ms162169.aspx
Looks like you have too many parenthesizes, but I think the actual error is coming from the leading space you have in your query: V str = "CREATE TABLE [ " & tablename & "] Try changing it to: str = "CREATE TABLE [" & tablename & "] ([Username] varchar(50),...
You can ty this PL/SQL Code: begin EXECUTE IMMEDIATE 'CREATE TABLE Shop ( GUID NUMBER(16), STATUS NUMBER(1), NAME VARCHAR2(50 BYTE), ) LOGGING NOCOMPRESS NOCACHE NOPARALLEL MONITORING'; DBMS_OUTPUT.PUT_LINE('DONE:'); INSERT INTO Customer ( GUID, STATUS, NAME) VALUES (1,1,'xx'); EXCEPTION -- exception handlers begin WHEN OTHERS THEN -- handles all other errors DBMS_OUTPUT.PUT_LINE('Error...
database,oracle,oracle11g,oracle10g,ddl
You can extend a tablespace by adding an additional datafile to it or by extending an existing one. Since you currently seem to have a convention of uniformly sized files, I'd just add another one: ALTER TABLESPACE "ABC" ADD DATAFILE '/ora/db/user/abc5.db' SIZE 4194304000; This can be done with the database...
sql,database,indexing,oracle11g,ddl
Defining an index as local has nothing to do with the tablespace it's stored on. local refers to partitioned indexes being partitioned in the same way the table they refer to is, as opposed to a global partitioned index which has its own partitioning definition (or even defined as nonpartitioned),...
oracle,oracle11g,ddl,oracle11gr2
You can try CORT : www.softcraftltd.co.uk/cort It's free
You could use a CHECK constraint with following conditions: starting_date > prenotation_date + 3 starting_date < ending_date TRUNC(starting_date) = TRUNC(ending_date) starting_date > prenotation_date + 3 will make sure that the booking is allowed after 3 days of the reservation. starting_date < ending_date will make sure that the time at which...
You have a two typos: STARTING - is a reserved word - use backticks you forget closing parenthesis This script should work: CREATE TABLE EVENT ( ID VARCHAR(255) NOT NULL, DESCRIPTION VARCHAR(255), ENDING DATE, ISALLDAY TINYINT(1) default 0, PUBLICEVENT TINYINT(1) default 0, `STARTING` DATE, TITLE VARCHAR(255), CREATOR VARCHAR(255), LOCATION BIGINT,...
The problem is the comma (,) between column type definitions and the not null definitions. This can be resolved quite easily, dy dropping them. Additionally, as primary key implies unique not null, you can just drop the redundant not null clause on dept_id: CREATE TABLE Employees( dept_id varchar2(4) PRIMARY KEY,...
postgresql,concurrency,ddl,catalog
This is a bit of a wart in the implementation of IF NOT EXISTS for tables and schemas. Basically, they're an upsert attempt, and PostgreSQL doesn't handle the race conditions cleanly. It's safe, but ugly. If the schema is being concurrently created in another session but isn't yet committed, then...
The two triggers work in the same way, except that the first will probably take more time. Suppose your test table has a table with a million records. And you attempt to update some column for every record. Then first the update operation will happen, and only once updates on...
Use the following select to get the table(s) with mixed-cases in name SELECT table_name ucase,lower(table_name) lcase FROM information_schema.tables where table_type = 'BASE TABLE' and table_schema = 'public' and table_name ~ E'^[[:upper:]][^[:upper:]]' PostgreSQL string function lower and information_schema.tables and use PL/PGSQL SQL - DO to rename all tables that have mixed-case...
I don't think you need any documentation, the java docs are self explaining. If I understand you correctly you need a way to set a default value for a field. If yes please see the following code snippet. @Entity @Table(name = "my_entity") public class SomeEntity extends BaseEntity { public static...
django,postgresql,types,timestamp,ddl
ALTER TABLE merged_subscription ALTER COLUMN end_date TYPE timestamp USING to_timestamp(end_date, 'DD-MON-YYY'); Replace the placeholder col with the actual column name. Assuming that strings in end_date comply with the given (uncommon!) pattern. (?) Are you sure you don't have 'DD-MON-YYYY'? If you actually have a date column, you don't need a...
sql,datetime,ms-access,ddl,create-table
Time and procedure are reserved words, and therefore should be escaped: Create Table Appointments (DocID char(4) not null primary key, PatID char(8) not null, [Day] varchar(8) not null, [Time] datetime not null, [Procedure] varchar(50) null); Or better yet, find names that aren't reserved words: Create Table Appointments (DocID char(4) not...
The problem is with the line Distributor_ID Number(3) REFERENCES Movie_Distributors(Distributor_ID). According to the position of the asterisk in the error message, Movie_Distributors does not exist (or the user issues the create table statement may not have permissions to it)....
Try: ALTER TABLE UŽSAKYTOS_DALYS ADD CONSTRAINT UŽSAKYTOS_DALYS_UŽSAKYMAI_FK FOREIGN KEY (KODAS_UKS, ID_USS) REFERENCES UŽSAKYMAI(KODAS_UŽ, ID_USS) ON DELETE CASCADE; This is my best guess, I'm not too familiar with Oracle, or foreign keys in general. I only make them very occasionally (read: basically never)....
You hace to do it in two steps: SET ANSI_PADDING OFF -- create the table without the columns that need ANSI padding SET ANSI_PADDING ON -- alter the table to add the columns that need ANDI passing This is the only way to do it. If you look at the...
Given this sample data: CREATE TABLE Table1 (`ID` int, `Team` varchar(6), `Task` varchar(7)) ; INSERT INTO Table1 (`ID`, `Team`, `Task`) VALUES (1, 'Team01', 'Task_01'), (2, 'Team02', 'Task_02'), (3, 'Team02', 'Task_01') ; You would usually just do this SELECT Team, Task, COUNT(*) FROM Table1 GROUP BY Team, Task; to get this...
mysql,database,delimiter,mysql-error-1064,ddl
You need to change the delimiter when you run DDL statements like this. MySQL is interpreting your semicolon as the end of your CREATE EVENT query, not as a delimiter within it. Try this: DELIMITER $$ CREATE EVENT demo ON SCHEDULE EVERY 2 SECOND DO BEGIN update alarm set is_on=1;...
Assuming that your database connection is correct and opened and rs("Name") is the name of country this should work for you: <% dim rsSelected set con = Server.CreateObject("ADODB.Connection") con.open "DRIVER={Microsoft Access Driver (*.mdb)}; DBQ=" & Server.MapPath("WebData/DB.mdb") & ";" set rsUser = Server.CreateObject("ADODB.RecordSet") set rsUser= con.Execute("Select top 1 country FROM Users...
sql,sql-server-2008,triggers,ddl,ddl-trigger
You need to bear in mind that an alter statement can add more than one column. If you look at the XML generated for an alter column statement you will get a better idea: ALTER TABLE dbo.EventTest ADD NewColumn1 INT, NewColumn2 INT; ---------------------------- <EVENT_INSTANCE> <EventType>ALTER_TABLE</EventType> <PostTime>2015-06-30T14:28:30.790</PostTime> <SPID>67</SPID> <ServerName>XXXXXX</ServerName> <LoginName>XXXXXX</LoginName> <UserName>dbo</UserName>...
If you only want to drop the datafile to simulate a hardware failure, than you can simply delete them from the machine. You can know where are stored your datafile using a simple query : select name from V$DATAFILE; This will give the path to all data files. Edit :...
sql,oracle,oracle10g,ddl,database-metadata
but how to pull that information object wise. Pass the parameters properly. You could then customize your output. DBMS_METADATA.GET_DDL (object_type, object_name, object_owner) For example, to get the METADATA for all the tables in the user SCOTT. SQL> select dbms_metadata.get_ddl('TABLE',t.table_name, 'SCOTT') from USER_TABLES t; DBMS_METADATA.GET_DDL('TABLE',T.TABLE_NAME,'SCOTT') -------------------------------------------------------------------------------- CREATE TABLE "SCOTT"."DEPT" (...
sql,oracle,postgresql,sequence,ddl
Oracle 12c introduces Identity columns: CREATE TABLE SOMEUSERS ( SOMEUSERS_ID NUMBER(10) GENERATED ALWAYS AS IDENTITY CONSTRAINT SOMEUSERS__SOMEUSERS_ID__PK PRIMARY KEY, SOMEUSERS_NAME VARCHAR2(50) CONSTRAINT SOMEUSERS__SOMEUSERS_NAME__NN NOT NULL, SOMEUSERS_PASSWORD VARCHAR2(50) ); If you want to do it in earlier versions then you will need a trigger and a sequence: CREATE TABLE SOMEUSERS (...
sql,sql-server,data-warehouse,ddl,materialized-views
No this isn't possible with SQL Server indexed views (indexing is the materialized view mechanism in SQL Server) There are very many limitations on constructs they may contain and self joins, sub queries, and tne OVER clause/ranking/aggregate window functions are all listed among these. This last restriction rules out using...
database,dictionary,filesystems,relational-database,ddl
(1) You have a false perception of what a DDL compiler does. First and foremost, the compiler does nothing else than compile your statements. If those statements are also to be executed then it is the DDL processor that does this. (Granted, it is often not very visible as being...
What do you mean "downside"? There is a really big downside if you don't make the column big enough -- you can't use it to store the values you want to store there. As for additional overhead, you don't need to worry about that. A varchar() type basically only takes...
Not having received any definite solution for this question, I have proceeded to cobble up a proof of concept option (since MySQL natively would not let you run SQL code that creates a trigger, using Prepared Statements). Please feel free to make any positive input. DELIMITER // DROP PROCEDURE IF...
oracle,multiple-columns,ddl,alter-table
You have to use the following statement to drop more than one columns: alter table table_name drop (col_name1, col_name2); Then you have to delete the "column" keyword in your statement. ...
sql,sql-server,sql-server-2008-r2,database-migration,ddl
Have you looked at Data Tools (also known as SSDT)? It's an add-in to Visual Studio that allows you to have (among other things) database projects. You add the objects in a declarative manner (i.e. create table, create procedure, etc) and the tool figures out how to make a target...
You cannot add a foreign key relationship to just any column. It needs to have an index. The easiest method is: CREATE TABLE usuario ( id smallint unsigned auto_increment primary key, name varchar(20) not null unique ); However, the correct method is to use primary keys for the relationship: CREATE...
mysql,sql,database,foreign-keys,ddl
A foreign key must reference a primary or unique key. For example, you could make countries.cname unique: CREATE TABLE countries ( id INT NOT NULL AUTO_INCREMENT, cname VARCHAR(45) NOT NULL, PRIMARY KEY (id), UNIQUE (cname) -- Here ) COMMENT='Country List'; Alternatively, you can drop the salt column and make the...
I found the answer, the DEFAULT CHARSET clause was mismatched. The second create table statement needs to also declare the DEFAULT CHARSET. DROP TABLE IF EXISTS `book` ; DROP TABLE IF EXISTS `person` ; CREATE TABLE `person` ( `name` VARCHAR(30) NOT NULL, PRIMARY KEY (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE...
index is a reserved word in MySQL (and any other relational database I can think of). It's used to create, well, indexes. In MySQL you can escape object names by enclosing them with ` characters: CREATE TABLE mytable ( tag MEDIUMTEXT, `index` BIGINT(20) ) ENGINE MyISAM; But it's generally considered...
mysql,transactions,persistence,rdbms,ddl
Executing a DDL statement causes an "implicit commit". But the statement has to be executed. The scenario you give doesn't make sense to me, the createStatement method doesn't take a string as an argument, does it? We typically see stmt = conn.createStatement(); stmt.execute("CREATE TABLE ... "); If you actually execute...
As usual, the fine manual is the place to go: CREATE [ OR REPLACE ] FUNCTION CREATE OR REPLACE FUNCTION will either create a new function, or replace an existing definition. ...
Try the below query for view: select text from ALL_VIEWS where upper(view_name) like upper(<view_name>; For mviews: select query from ALL_MVIEWS where upper(mview_name) like upper(<mview_name>); ...
oracle,oracle11g,export,ddl,dbms-scheduler
According to the documentation we have to use Data Pump to export DBMS_SCHEDULER objects: "An export generates the DDL that was used to create the Scheduler objects. All attributes are exported. When an import is done, all the database objects are recreated in the new database." Find out more about...
Disallowing attribute1 from being larger than attribute2 can be done with a check constraint: ALTER TABLE mytable ADD CONSTRAINT attribute2_greater_check CHECK (attribute2 >= attribute1) Preventing update of attribute2 can be done with a trigger that raises an error: CREATE OR REPLACE TRIGGER mytable_attribute2_update_tr BEFORE UPDATE ON mytable FOR EACH ROW...
Your statement is not valid MySQL syntax and it doesn't make sense. The problem with the syntax is that offset is not supported for update statements (see here). The problem with the logic is that you have no order by clause. MySQL doesn't guarantee the order of tables when processing...
postgresql,database-design,primary-key,ddl,composite-primary-key
There can only be one primary key per table. That's what the word "primary" hints at. You can have additional UNIQUE columns. CREATE TABLE test( sl_no int PRIMARY KEY, -- NOT NULL automatically emp_id int UNIQUE NOT NULL, emp_name text, emp_addr text ); Or to make that a single multicolumn...
java,code-generation,database-schema,ddl,jooq
Jooq needs some tables to be created before hand. You can use Flyway for that (and you should use its migrations too). Once you have all your tables, you can use this snippet to have Jooq generate the source files for your tables: import org.jooq.util.GenerationTool; import org.jooq.util.jaxb.*; Configuration configuration =...
mysql,transactions,innodb,database-schema,ddl
BEGIN in some contexts is the same as START TRANSACTION. phpmyadmin performs one query at a time unless you put the batch in the window for such. Try that. (And SET autocommit = 0 is irrelevant because of the explicit START.) Edit Since the "transaction" really had a DDL statement,...
There are a few issues here: First the on delete cascade is part of the foreign key definition, so the comma (,) before it should be removed. Second, the second foreign key references url, which is not a unique key, and therefore is not allowed. So either remove this constraints:...
same issue addressed here: How to get SQL text from Postgres event trigger bottom line, as of now, wait until more features are added to EVENT TRIGGER in future releases...
postgresql,types,auto-increment,ddl,postgresql-9.3
serial is a pseudo data type, not an actual data type. It's an integer underneath with some additional DDL commands executed automatically: Create a sequence (with matching name by default). Set the column NOT NULL and the default to draw from that sequence. Make the column "own" the sequence. Details:...
Remove the single quotes. Try this: CREATE TABLE IF NOT EXISTS comments ( id int(10) unsigned NOT NULL AUTO_INCREMENT, article_id int(10) NOT NULL, comment varchar(45) NOT NULL, time datetime NOT NULL, name varchar(45) NOT NULL, email varchar(45) NOT NULL, PRIMARY KEY (id), KEY 'fk_comments_article'(article_id) ); or try with back ticks:...