dstr_sql = dstr_sql & " SELECT B.COLUMN_NAME "
dstr_sql = dstr_sql & " From USER_CONSTRAINTS A, USER_CONS_COLUMNS B "
dstr_sql = dstr_sql & " WHERE A.constraint_name = B.constraint_name "
dstr_sql = dstr_sql & " and A.constraint_type='P' "
dstr_sql = dstr_sql & " AND B.table_name='DB_koji'"
小弟我用上面的方法可是却查到A.constraint_type='C' (NOT NULL 都是'C' )我想要主键该如何是好
dstr_sql = dstr_sql & " From USER_CONSTRAINTS A, USER_CONS_COLUMNS B "
dstr_sql = dstr_sql & " WHERE A.constraint_name = B.constraint_name "
dstr_sql = dstr_sql & " and A.constraint_type='P' "
dstr_sql = dstr_sql & " AND B.table_name='DB_koji'"
小弟我用上面的方法可是却查到A.constraint_type='C' (NOT NULL 都是'C' )我想要主键该如何是好
把AND B.table_name='DB_koji'拿掉试试吧。
或者这样AND substrb(B.table_name,1,7)='DB_koji'
SELECT B.COLUMN_NAME||' '||a.constraint_type
From USER_CONSTRAINTS A, USER_CONS_COLUMNS B
WHERE A.constraint_name = B.constraint_name
and A.constraint_type='P'
AND B.table_name='DB_koji'
/
SELECT "SYS"."USER_TAB_COLUMNS"."COLUMN_NAME",
"SYS"."USER_TAB_COLUMNS"."DATA_TYPE",
"SYS"."USER_TAB_COLUMNS"."DATA_LENGTH",
"SYS"."USER_TAB_COLUMNS"."COLUMN_ID",
A.POSITION
FROM "SYS"."USER_TAB_COLUMNS" ,
(SELECT "SYS"."USER_CONS_COLUMNS"."COLUMN_NAME" COLUMN_NAME,
"SYS"."USER_CONS_COLUMNS"."POSITION" POSITION
FROM "SYS"."USER_CONS_COLUMNS",
"SYS"."USER_CONSTRAINTS"
WHERE ( "SYS"."USER_CONS_COLUMNS"."CONSTRAINT_NAME" = "SYS"."USER_CONSTRAINTS"."CONSTRAINT_NAME" ) and
( "SYS"."USER_CONSTRAINTS"."TABLE_NAME" = :stable ) AND
( "SYS"."USER_CONSTRAINTS"."CONSTRAINT_TYPE" = 'P' )
) A
WHERE "SYS"."USER_TAB_COLUMNS"."TABLE_NAME" = :stable AND
"SYS"."USER_TAB_COLUMNS"."COLUMN_NAME" = A."COLUMN_NAME" (+)
ORDER BY "SYS"."USER_TAB_COLUMNS"."COLUMN_ID";