Path: utzoo!attcan!uunet!mcvax!ukc!reading!riddle!john
From: john@riddle.UUCP (Jonathan Leffler)
Newsgroups: comp.databases
Subject: Re: Referential Integrity
Summary: The next version of ANSI SQL will have it
Message-ID: <956@riddle.UUCP>
Date: 3 Jan 89 09:20:10 GMT
References: <560@bloom.UUCP>
Reply-To: john@riddle.UUCP (Jonathan Leffler)
Organization: Sphinx Ltd., Maidenhead, England
Lines: 95

In article <560@bloom.UUCP> bobd@bloom.UUCP (Bob Donaldson) writes:
>Why don't any of the RDBMS packages we all know and love REALLY support
>referential integrity??

There is an ANSI SQL committee working on the next versions (yes,
plural) of ANSI SQL -- known as SQL2 and SQL3.  I don't have up
to date info, but as of March 1988, SQL2 (and therefore SQL3 too)
had the following (outline) syntax in BNF:

<table definition> ::=
		CREATE TABLE <table name>
		(	<table element> [ { , <table element> } ... ] )

<table element>	::=
		<column definition>
	|	<table constraint definition>

<table constraint definition> ::=
		<table constraint>
		<constraint name definition>

<table constraint> ::=
		<unique constraint definition>
	|	<referential constraint definition>
	|	<check constraint definition>

<column definition> ::=
		<column name> [ <data type> |  <domain name> ]
		[ <default clause> ]
		[ <column constraint definition> ... ]

<column constraint definition> ::=
		<column constraint>
		<constraint name definition>

<column constraint> ::=
		NOT NULL
	|	<unique specification>
	|	<references specification> [ <triggered action> ]
	|	CHECK ( <search condition> )

<unique constraint definition> ::=
		<unique specification> ( <unique column list> )

<unique specification> ::=
		UNIQUE | PRIMARY KEY

<referential constraint definition> ::=
		<referential constraint>
		[ <triggered action> ]

<referential constraint> ::=
		FOREIGN KEY ( <referencing columns> )
		<references specification>

<references specification> ::=
		REFERENCES <referenced table and columns>
		[ MATCH <match columns> ]

<match columns> ::=
		NONE | ALL

<triggered action> ::=
		<update rule> [ <delete rule> ]
	|	<delete rule> [ <update rule> ]

<update rule> ::=
		ON UPDATE <referential action>

<delete rule> ::=
		ON DELETE <referential action>

<referential action> ::=
		CASCADE
	|	SET NULL
	|	SET DEFAULT

There are a good many definitions I have not listed; they are
either not relevant (well, not very) or trivial (e.g. a list of
things is a thing or a thing followed by a comma and a list of
things).

There are many pages of spiel about what it all means, but this
should give you:

	a)	An idea of how turgid standards documents can be.

	b)	How referential integrity will be specified.

I haven't yet worked out the method of specifying a RESTRICTED
(or BLOCK) constraint -- I think you omit the <triggered action>.

I hope this is some help.

Jonathan Leffler (john@sphinx.co.uk)