FAQ Database Discussion Community


Different triggers same job?

sql,triggers,ddl
I have two triggers and I would like to know if the result of the work performed is the same. CREATE TRIGGER trg_test_upd ON test AFTER UPDATE AS RAISERROR('Error', 16, 1); ROLLBACK; GO CREATE TRIGGER trg_test_upd ON test INSTEAD OF UPDATE AS RAISERROR('Error', 16, 1); GO Second question, why would...

SQL DDL to support migration and updates

sql,sql-server,sql-server-2008-r2,database-migration,ddl
I have a web app that uses a basic ms-sql DB only tables and constraints nothing special. Every time i need to upgrade the schema of my DB I delete the old one and recreate a new one using a standard DDL script with a lot of create table clauses....

Syntax error preventing CREATE EVENT

mysql,database,delimiter,mysql-error-1064,ddl
CREATE EVENT demo ON SCHEDULE EVERY 2 SECOND DO BEGIN update alarm set is_on=1; END Error SQL query: CREATE EVENT demo ON SCHEDULE EVERY 2 SECOND DO BEGIN update alarm set is_on=1; MySQL said: Documentation 1064 - You have an error in your SQL syntax; check the manual that corresponds...

Set ANSI_PADDING on a specific column (ANSI_PADDING ON and OFF in different columns of the same table)

sql,sql-server,ddl
Is it possible - using a single CREATE TABLE script - to set specific varbinary columns ANSI_PADDING = ON but others to ANSI_PADDING = OFF? e.g. CREATE TABLE PaddingX ( ... ColumnA varbinary(max), ... ColumnB varbinary(50) ) I would like ColumnA to trim (padding OFF), but ColumnB to contain the...

exporting db objects for version control

sql,oracle,oracle10g,ddl,database-metadata
We are implementing version control for our project. As part of this we need to check in all DB objects. We have tables, procedures, functions, packages, view and materialized view. Problem is there are many objects and we need to put source code file wise. e.g. There are tables T1,...

Are there downsides for creating a large VARCHAR value in Redshift?

sql,ddl,amazon-redshift
The source data keeps throwing values for the field that keep on getting bigger and bigger in length. Right now I'm using VARCHAR(200) but I might go for VARCHAR(400). Is there any disadvantages for using a large number?

Constraint on values, oracle database

sql,oracle,ddl
I would like to have a table in my Oracle database where the values of an attribute1 (values may change) can't be greater than the value (fixed) of attribute2. Is it possible to enforce such a rule? Is it possible to make a value impossible to change after the insert...

ORA-1691: unable to extend lobsegment

database,oracle,oracle11g,oracle10g,ddl
I'm getting this error: ORA-1691: unable to extend lobsegment ABC.SYS_LOB0014859757C00018$$ by 1280 in tablespace ABC The tablespace is build like the folowing: CREATE TABLESPACE "ABC" DATAFILE '/ora/db/user/abc1.db' SIZE 4194304000, '/ora/db/user/abc2.db' SIZE 4194304000, '/ora/db/user/abc3.db' SIZE 4194304000, '/ora/db/user/abc4.db' SIZE 4194304000 LOGGING ONLINE PERMANENT BLOCKSIZE 8192 EXTENT MANAGEMENT LOCAL UNIFORM SIZE 10485760 SEGMENT...

MySQL FOREIGN KEY error, ON DELETE CASCADE

mysql,sql,foreign-keys,ddl
I have absolutely no clue why MySQL is having an issue with the second CREATE TABLE statement. CREATE TABLE User( uid INTEGER, url CHAR(100), firstname CHAR(40), lastname CHAR(40), PRIMARY KEY(uid) ); The below is the one that causes problems: CREATE TABLE Follows( uid INTEGER, url CHAR(100), PRIMARY KEY(uid,url), FOREIGN KEY(uid)...

Oracle: Many to Many: Requires two Foreign Key Constraints?

sql,oracle,ddl
Im very new to SQL and i tried to create a many to many relationship: CREATE TABLE HOUSE_USER ( USER_ID NUMBER(10) NOT NULL, USER_EMAIL VARCHAR(255) NOT NULL, USER_PASSWORD VARCHAR(255) NOT NULL, CONSTRAINT USER_PK PRIMARY KEY(USER_ID), CONSTRAINT PROFILE_FK FOREIGN KEY(PROFILE_ID) REFERENCES HOUSE_PROFILE(PROFILE_ID) ); CREATE TABLE HOUSE_USER_GROUPE ( USER_GROUPE_ID NUMBER(10) NOT NULL,...

Create Table - Time Statement

sql,datetime,ms-access,ddl,create-table
I am having trouble trying to create a table using MS Access. I know for sure my problem lies in the "datetime" field but I can not figure what I am missing or doing wrong. When I click "run" I get the "Syntax Error in Field Definition" Create Table Appointments...

Auto-COmmit of DDL

mysql,transactions,persistence,rdbms,ddl
I read that "CREATE TABLE" and "DROP TABLE" statements are auto-commit. I am trying to understand their behaviour for the below scenario: conn.setAutoCommit(false); stmt = conn.createStatement("CREATE TABLE ..."); conn.commit(); In the above case, would setting autoCommit to false would have any impact on CREATE TABLE auto-commiting. If conn.setAutoCommit(false) does override...

Oracle sql: Drop table column shows error: invalid identifier? [closed]

oracle,multiple-columns,ddl,alter-table
I get this error when I am trying to drop multiple column in one statement:- ALTER TABLE "RLX_ROLES" DROP COLUMN ("test","test_2"); But it works if I drop them one by one:- ALTER TABLE "RLX_ROLES" DROP COLUMN "test"; ALTER TABLE "RLX_ROLES" DROP COLUMN "test_2"; Why so? Is my syntax not correct?...

Generate upgrade script to ALTER TABLE based on CREATE TABLE statement in Oracle [duplicate]

oracle,oracle11g,ddl,oracle11gr2
This question already has an answer here: Automated Oracle Schema Migration Tool [closed] 5 answers Is it possible in Oracle to generate a bunch of ALTER TABLE statements based on existing table in schema plus CREATE TABLE statement with newer definition of that table? Let's say I have a...

Do I need to move local index after table moved to different tablespace

sql,database,indexing,oracle11g,ddl
I am little confused about moving a table to different tablespace when it has couple of indexes which are local indexes, I suppose this means that the indexes are stored in some tablespace same as owner table, so when I moved the table to different tablespace then the local indexes...

DDL date data type in check constraint manipulation in oracle

oracle,date,constraints,ddl
i'm creating a table that should check various date constraint, but oracle is kind ticky on this argument. My goal is to reserve a certain room for let's say a lecture, so i tought of something like this : .... prenotation_date date NOT NULL, starting_date date NOT NULL, ending_date date...

Dynamically constructing MySQL Code for creating a trigger

mysql,ddl,audit-trail
I am trying to implement audit trail/logging for a MySQL database. Now I have created an "audit" db (let's call it company_audit) which will contain "audit" copies of certain tables in the main db. (Say for company.customers I will create company_audit.customers. The audit table will contain the same exact columns...

SQL SERVER FK and Constraint

sql,sql-server,foreign-keys,constraints,ddl
I'm working with SQL SERVER 2000 and I have this code for creating a table. My table has foreign keys and I wish to add a constraint. My Question is: Must I define the constraint AND the foreign key? or is just one of them enough? CREATE TABLE controls (...

php mysql alter table query error

php,mysql,sql,mysqli,ddl
The following mysqli call $strSQL3=mysqli_query($connection," alter table mark_list add column 'mark' int(2) " ) or die(mysqli_error($connection)); returns error You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near mark int(2) at line 1 ...

How can I delete the users datafile in Oracle using SQL?

oracle,ddl,tablespace
I have tried dropping the datafile by itself but I get this error. SQL> alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf'; alter tablespace USERS drop datafile '/u01/app/oracle/oradata/orcl/users01.dbf' * ERROR at line 1: ORA-03261: the tablespace USERS has only one file I have also tried taking the tablespace offline and dropping the...

Create Table DDL causes sql server to hang

sql,sql-server,ddl
Is there anything in this DDL statement that could cause the system to hang and create long running query? -- Abort on any error SET XACT_ABORT ON GO -- FUll isolation SET TRANSACTION ISOLATION LEVEL SERIALIZABLE GO BEGIN TRANSACTION GO CREATE TABLE ClientPayerCrosswalkMapping ( id INT IDENTITY(1, 1) NOT NULL,...

SQL trigger for expiration date

sql,sql-server,triggers,ddl
I have a table with the columns expiration_date and isExpired. I want to create a trigger to set the column isExpired to 1 if the column expiration_date passes the current date. I have no experience with triggers....

CREATE SCHEMA IF NOT EXISTS raises duplicate key error

postgresql,concurrency,ddl,catalog
To give some context, the command is issued inside a task, and many task might issue the same command from multiple workers at the same time. Each tasks tries to create a postgres schema. I often get the following error: IntegrityError: (IntegrityError) duplicate key value violates unique constraint "pg_namespace_nspname_index" DETAIL:...

Specifying a triger to update timestamp field

sql,postgresql,triggers,updates,ddl
This is a follow up question on : old example. CREATE TABLE "x"."y"( "z" timestamp NOT NULL, "a" Timestamp NOT NULL DEFAULT z + 18 months, ); I wish to specify a trigger where a is updated as soon as z is updated. Something on the lines of CREATE TRIGGER...

Get table definition in oracle sql plus [duplicate]

sql,oracle,sqlplus,ddl,database-metadata
This question already has an answer here: How to get Oracle create table statement in SQL*Plus 2 answers I'm new to sql and I have created table using create table table_name. And now I want to get this table definition or let's say source code, I know command desc...

create table in access database using Adox in vb

vb.net,vba,ddl,adox
I'm using a Windows form application in Visual Basic 2012 to create a new Microsoft Access database using .ADOX. I can create the database but can't create a table in the database. My code is : Imports ADOX Imports System.Data.OleDb Public Class Form1 Dim mycommand As OleDbCommand Dim myconnection As...

ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE

oracle,oracle11g,export,ddl,dbms-scheduler
I was trying to exported the complete scheduler chain created on Oracle 11g (including steps, rules, etc), when I got this error : ORA-31600: invalid input value CHAIN for parameter OBJECT_TYPE in function GET_DDL ORA-06512: at "SYS.DBMS_METADATA", line 5805 ORA-06512: at "SYS.DBMS_METADATA", line 8344 ORA-06512: at line 1 Message meaning...