StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>You have already stated that INSTANCEID is supposed to be UNIQUE, so a constraint has been created.</p> <pre><code>CREATE TABLE INSTANCES ( DM INTEGER NOT NULL, INSTANCEID VARCHAR2(512) NOT NULL UNIQUE, -- UNIQUE constraint INSTANCENAME VARCHAR2(64) NOT NULL UNIQUE, HOSTNAME VARCHAR2(32) NOT NULL, CONSTRAINT PK_INSTANCES PRIMARY KEY (INSTANCEID, HOSTNAME) ); </code></pre> <p><strong>Edit</strong>: Ok, after reading your comment, try this:</p> <pre><code>SELECT con.constraint_name, col.column_name, con.constraint_type FROM user_cons_columns col JOIN user_constraints con ON (col.constraint_name = con.constraint_name) WHERE col.table_name = 'INSTANCES' AND con.constraint_type = 'U' ; </code></pre> <p>It will list UNIQUE constraints and associated columns for INSTANCE table. Please check if there is a unique constraint on the INSTANCEID column (and if that constraint has no other associated columns).</p> <p>Example at SQLFiddle: <a href="http://sqlfiddle.com/#!4/43b43/6" rel="nofollow">http://sqlfiddle.com/#!4/43b43/6</a></p> <p><strong>Edit #2</strong>: creating named constraints, all options:</p> <pre><code>-- CREATE TABLE - "In Line" Constraints CREATE TABLE ports ( ID NUMBER CONSTRAINT PORT_ID_PK PRIMARY KEY, NAME VARCHAR2(20) ); CREATE TABLE ports ( ID NUMBER, NAME VARCHAR2(20) CONSTRAINT NAME_NN NOT NULL ); CREATE TABLE ports ( ID NUMBER, NAME VARCHAR2(20) CONSTRAINT NAME_UQ UNIQUE ); CREATE TABLE ports ( ID NUMBER, STATUS NUMBER CONSTRAINT PROPER_STATUS_CK CHECK (STATUS IN (4, 5)) ); CREATE TABLE ships ( SHIP_ID NUMBER, NAME VARCHAR2(20), HOME_PORT_ID NUMBER CONSTRAINT SHIP_PORT_FK REFERENCES PORTS (ID) ); -- CREATE TABLE - "Out of Line" Constraints CREATE TABLE ports ( ID NUMBER, NAME VARCHAR2(20), CONSTRAINT PORT_ID_PK PRIMARY KEY (ID) ); -- NOT NULL constraints can not be created "Out of Line"! CREATE TABLE ports ( ID NUMBER, NAME VARCHAR2(20), CONSTRAINT NAME_UQ UNIQUE (NAME) ); CREATE TABLE ports ( ID NUMBER, STATUS NUMBER, CONSTRAINT PROPER_STATUS_CK CHECK (STATUS IN (4, 5)) ); CREATE TABLE ships ( SHIP_ID NUMBER, NAME VARCHAR2(20), HOME_PORT_ID NUMBER, CONSTRAINT SHIP_PORT_FK FOREIGN KEY (HOME_PORT_ID) REFERENCES PORTS (ID) ); -- ALTER TABLE - "In Line" Constraints ALTER TABLE PORTS MODIFY ID CONSTRAINT PORT_ID_PK PRIMARY KEY; ALTER TABLE PORTS MODIFY NAME CONSTRAINT NAME_NN NOT NULL; ALTER TABLE PORTS MODIFY NAME CONSTRAINT NAME_UQ UNIQUE; ALTER TABLE SHIPS MODIFY HOME_PORT_ID CONSTRAINT SHIP_PORT_FK REFERENCES PORTS (ID); -- ALTER TABLE - "Out of Line" Constraints ALTER TABLE PORTS ADD CONSTRAINT PORT_ID_PK PRIMARY KEY (ID); -- NOT NULL constraints can not be created "Out of Line"! ALTER TABLE PORTS ADD CONSTRAINT NAME_UQ UNIQUE (NAME); ALTER TABLE PORTS ADD CONSTRAINT PROPER_STATUS_CK CHECK (STATUS IN (4, 5)); ALTER TABLE SHIPS ADD CONSTRAINT SHIP_PORT_FK FOREIGN KEY (HOME_PORT_ID) REFERENCES PORTS (ID); </code></pre> <p>NOT NULL constraints cannot be create of out line.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload