Displaying the constraints in a table
Solution 1
select dbms_mview.get_ddl('TABLE',USER,'TEAMS') from dual;
Solution 2
Try this:
SELECT constraint_name,
constraint_type,
search_condition
FROM USER_CONSTRAINTS
WHERE table_name = 'TEAMS';
Unless double-quoted when created, all object names in Oracle are upper case.
Solution 3
I personally use:
SELECT * FROM all_constraints WHERE Table_Name = <TableName>;
Solution 4
Use the following code:
show create table table_name;
Solution 5
If you prefer the CamelCase names, your create table script should have been:
Create table "Teams" (
"TeamID" varCHAR2(4) constraint "Teams_TeamID_PK" Primary Key,
"TeamName" VARCHAR2(40)
);
Without double-quotes Oracle helpfully converts all identifiers to uppercase :)
Michael
Updated on July 12, 2022Comments
-
Michael almost 2 years
Hello I am trying to display the constraints in one of my tables but for some reason I get the message no rows selected. Noted below is the table I have created.
Create table Teams ( TeamID varCHAR2(4) constraint Teams_TeamID_PK Primary Key, TeamName VARCHAR2(40) );
This is the code I am using to show my constraints.
SELECT constraint_name, constraint_type, search_condition FROM USER_CONSTRAINTS WHERE table_name = 'Teams';
I am a rookie so I want to make sure I understand what is wrong. I have tried to drop the table thinking that my constraints did not take - I did not, nor did I receive any errors when I created the table and I am referencing TeamID in another table. So when I try to drop the table I get an error message when is what I was hoping for.
-
Anand Murali about 10 yearsWill work in MySQL but not in MS SQL Server and Oracle.
-
Ema.jar about 7 yearsThe SQL keywords are case insensitive: stackoverflow.com/questions/153944/is-sql-syntax-case-sensitive