1

I would like to have some suggestions on how to display all the CONSTRAINTs of a created table in SQL.

I created a table:

CREATE TABLE ACCOUNT(
    USERNAME      VARCHAR(15)     UNIQUE,
    PASSWORD      VARCHAR(15),
    CONSTRAINT    ACCOUNT_UNIQUE  UNIQUE(USERNAME),
    CONSTRAINT    ACCOUNT_PK      PRIMARY KEY(USERNAME,PASSWORD)

I would like to discover these constraints because when I do / or RUN command in the SQL command line, I can't display those constraints anymore.

Is there a way to display all the CONSTRAINTs for the table ACCOUNT?

Pang
  • 9,564
  • 146
  • 81
  • 122
Asynchronousx
  • 101
  • 1
  • 1
  • 8
  • Possible duplicate of [Which Oracle view contains all constraints together?](http://stackoverflow.com/questions/3786156/which-oracle-view-contains-all-constraints-together) – Kris Johnston May 12 '17 at 17:09

1 Answers1

0

For Oracle: 

SELECT * 
FROM USER_CONSTRAINTS 
WHERE table_name = 'ACCOUNT';

For mySQL

You can use DESCRIBE ACCOUNTto view the table structure, included the constraints, or also SHOW CREATE TABLE ACCOUNT to see the creation code, which includes the constraints.

Another way is: 

SELECT COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'ACCOUNT';
Spock
  • 315
  • 2
  • 13
  • Already checked, when i do this on the command line i get that : SQL> DESCRIBE ACCOUNT; Name Null? Type ----------------------------------------- -------- ---------------------------- USERNAME NOT NULL CHAR(15) PASSWORD NOT NULL CHAR(15) but not the constrains. – Asynchronousx May 12 '17 at 16:40
  • Thank you for your kindness and fast answer, but when i run this code i get this error :ERROR at line 2: ORA-00942: table or view does not exist. also im not using any graphic interface but just the SQL Command Line prompt editor. – Asynchronousx May 12 '17 at 16:42
  • @Asynchronousx, oh, that's because I've thougt you are using mySQL... I've added also the SELECT for oracle now... I hope that this time it works :) – Spock May 12 '17 at 21:49