Possible in several databases, not not standardised. Useful because it lets you write complex structures without having to worry about maintaining referential integrity at every operation - it all gets checked in one big bang at commit. Not necessarily what you'd always want, but useful to be able to do.
http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/clauses002.htm#sthref2934 http://ss64.com/ora/syntax-constraints.html - options are a bit complex.
http://www.postgresql.org/docs/8.2/static/sql-set-constraints.html - FK only, so not, eg, uniques (?).