It is always a question that how do I know what constraints are there on each column of a table. Following query gives access to this information,
Above query returns all the tables and all its columns with some constraints.
CONSTRAINT_TYPE column in table USER_CONSTRAINTS store information about what type of constraint a column is. The mapping goes this way,
1. C --> Check/Not Null
2. P --> Primary Key
3. R --> Foreign Key
4. U --> Unique
Since CONSTRAINT_TYPE defines both CHECK and NOT NULL as C, Column SEARCH_CONDITION gives more details on CONSTRAINT_TYPE C.
If you are only interested only in knowing NOT NULL columns for a table then just use DESCRIBE <tablename>. This lists columns with not null constraint.
Sample output of the above query on my database is as below,
SELECT a.table_name,a.column_name,a.constraint_name, b.constraint_type, b.search_condition
FROM USER_CONS_COLUMNS a, USER_CONSTRAINTS b
WHERE a.constraint_name = b.constraint_name
ORDER BY a.table_name;
Above query returns all the tables and all its columns with some constraints.
CONSTRAINT_TYPE column in table USER_CONSTRAINTS store information about what type of constraint a column is. The mapping goes this way,
1. C --> Check/Not Null
2. P --> Primary Key
3. R --> Foreign Key
4. U --> Unique
Since CONSTRAINT_TYPE defines both CHECK and NOT NULL as C, Column SEARCH_CONDITION gives more details on CONSTRAINT_TYPE C.
If you are only interested only in knowing NOT NULL columns for a table then just use DESCRIBE <tablename>. This lists columns with not null constraint.
Sample output of the above query on my database is as below,
Comments
More than 10 rows available. Increase rows selector to view more rows.
Where is that rows selector and how to Increase rows selector?? Pls help me out. Thank you.
Once you are under "SQL Commands" row selector is available in the same line as "RUN" button.You would see "Rows" next to it there is a pull down menu in which you can choose more than 10 rows to be applied you can choose till 100000 rows, if there are more rows than 100000 then you will have to download on to a file.
Regards,
Mithun
You can use the following SQL statements to find default tables,
SELECT * FROM TAB;
or
SELECT TABLE_NAME FROM USER_TABLES;
I am a beginner in Oracle SQL
which books should I refer to ; to learn subqueries and joins?
Thanks in advance.
I wanted to know how can we retrieve a specific record from the table. lets say 3rd record. (without knowing any value in that record)
Am Jyothi, your old student from qspiders
Can you please tell me How to write a query to find columns has BLANK values or not?? and
How to write a query to find columns has NULL values or not
DROP TABLE IBU1;
DROP TABLE IBU3;
DROP TABLE IBU5;
CREATE TABLE IBU1 AS select column_name AS C1 from all_tab_columnS where table_name='Your_Table_name' AND NULLABLE='N';
CREATE TABLE IBU3 AS SELECT CONSTRAINT_NAME,IBU1.C1 FROM ALL_CONS_COLUMNS,IBU1 WHERE ALL_CONS_COLUMNS.COLUMN_NAME=IBU1.C1 AND ALL_CONS_COLUMNS.TABLE_NAME='Your_Table_Name';
CREATE TABLE IBU5 AS SELECT CONSTRAINT_nAME FROM usER_CONSTRAINTS WHERE CONSTRAINT_TYPE='C' AND TABLE_NAME='Your_Table_Name';
SELECT IBU3.CONSTRAINT_NAME FROM IBU3,IBU5 WHERE IBU3.CONSTRAINT_NAME=IBU5.CONSTRAINT_NAME;