SQL trigger for expiration date
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...
Renaming multiple columns in one statement with PostgreSQL
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...
Get table definition in oracle sql plus [duplicate]
sql,oracle,sqlplus,ddl,database-metadata
Check the function: DBMS_METADATA.GET_DDL http://psoug.org/reference/dbms_metadata.html...
Oracle - Transaction with a series of DDL statements
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...
How to prevent DDL, DCL, TCL commands in Oracle Query
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...
Oracle: Many to Many: Requires two Foreign Key Constraints?
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...
Change data type of a table column from timestamp to bigint
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,...
Collect all non-numeric values from a column after GROUP BY clause in MYSQL
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; ...
Modify a column to remove or change default value in oracle 11g
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 SERVER FK and Constraint
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....
Create Table DDL causes sql server to hang
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) ); ...
Is there a postgres command to list/drop all materialized views?
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...
Specifying a triger to update timestamp field
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...
.Net SQL Server DDL API [closed]
Sql Server Management Objects http://msdn.microsoft.com/en-us/library/ms162169.aspx
create table in access database using Adox in vb
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),...
Using EXECUTE IMMEDIATE inside PL\SQL Block
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...
ORA-1691: unable to extend lobsegment
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...
Do I need to move local index after table moved to different tablespace
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),...
Generate upgrade script to ALTER TABLE based on CREATE TABLE statement in Oracle [duplicate]
oracle,oracle11g,ddl,oracle11gr2
You can try CORT : www.softcraftltd.co.uk/cort It's free
DDL date data type in check constraint manipulation in oracle
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 an error in your SQL syntax [MariaDB] [closed]
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,...
ORA-00906 Missing left paranthesis error
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,...
CREATE SCHEMA IF NOT EXISTS raises duplicate key error
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...
Different triggers same job?
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...
Rename Table(s) In Postgresql
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...
Define default column value with annotations in Hibernate
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...
Convert data type of column to timestamp using explicit cast
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...
Create Table - Time Statement
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...
Error while creating table in Oracle SQL
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)....
can someone help me with a SQL Error: ORA-02270?
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)....
Set ANSI_PADDING on a specific column (ANSI_PADDING ON and OFF in different columns of the same table)
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...
SQL Script to define a table schema using values from another existing table in MYSQL
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...
Syntax error preventing CREATE EVENT
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;...
Selecting value in ddl from DB, Classic ASP
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...
Single out specific parts of EVENTDATA for use in a DDL Trigger?
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>...
How can I delete the users datafile in Oracle using SQL?
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 :...
exporting db objects for version control
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" (...
Sequence in Oracle/PostgreSQL with no ID in insert statement
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 (...
Using a Materialized View, can you generate a Two-Timestamp view from a One-Timestamp table?
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...
How is a data dictionary/table stored in memory?
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...
Are there downsides for creating a large VARCHAR value in Redshift?
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...
Dynamically constructing MySQL Code for creating a trigger
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 sql: Drop table column shows error: invalid identifier? [closed]
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 DDL to support migration and updates
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...
Error in MySQL on create statement
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...
Cannot add foreign key constraint mysql
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...
MySQL Cannot add foreign key constraint - mismatched charset
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...
error in SQL syntax creating a database table
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...
Auto-COmmit of DDL
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...
How to replace function in postgress?
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. ...
Get VIEW ddl using query
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>); ...
ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE
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...
Constraint on values, oracle database
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...
`UPDATE` and `LIMIT` in `MySQL`
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...
Primary key for multiple column in PostgreSQL?
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...
Generate jOOQ classes from pure Java
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 with schema modification statements (DDL)?
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,...
MySQL FOREIGN KEY error, ON DELETE CASCADE
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:...
How to get the name of the altered table in a Postgres event trigger?
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...
How to convert primary key from integer to serial?
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:...
SQL syntax error, Can't find it?
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:...