I'm learning SQL and what bothers me, is that I seem unable to find ALL constraints on a table. I created the table with
create table t2
(a integer not null primary key,
b integer not null, constraint c1 check(b>0),
constraint fk1 foreign key(a) references t1(a));
and added a constraint with
alter table t2
add constraint c2 check (b<20);
I then tried to see ALL (four) constraints with
show table status
from tenn #-->the name of my database
like 't2';
and then
show create table t2;
and then
select *
from information_schema.key_column_usage
where table_name='t2';
and finally
select *
from information_schema.table_constraints
where table_name='t2';
But none of these shows all four constraints. Could anyone tell me how to see all of them?
Thanks a lot!
解决方案select COLUMN_NAME, CONSTRAINT_NAME, REFERENCED_COLUMN_NAME, REFERENCED_TABLE_NAME
from information_schema.KEY_COLUMN_USAGE
where TABLE_NAME = 'table to be checked';