Re: Referential Integrity

On 11/16/2016 7:41 AM, Stefan Ram wrote:
> Does the following schema have referential integrity?
>
> \W SET sql_mode = 'ANSI,TRADITIONAL';
> DROP SCHEMA S; CREATE SCHEMA S; USE S;
>
> CREATE TABLE T ( C VARCHAR ( 255 ), D VARCHAR ( 255 ));
>
> CREATE INDEX I on T ( C );
>
> INSERT INTO T ( C, D ) VALUES ( '1', '1' );
> INSERT INTO T ( C, D ) VALUES ( '1', '2' );
>
> CREATE TABLE U
> ( C VARCHAR ( 255 ),
> D VARCHAR ( 255 ),
> FOREIGN KEY ( D ) REFERENCES T ( C ));
>
> INSERT INTO U ( C, D ) VALUES ( 'A', '1' );
>
> SELECT * FROM T;
>
> SELECT * FROM U;
>
> Observe that the value of the foreign key '1' from table U
> appears /twice/ in table T.
>

Yes. Referential integrity is not the same as unique. It just means that at least one instance of the value exists in the table being referenced.

-- 
==================
Remove the "x" from my email address
Jerry Stuckle
jstucklex_at_attglobal.net
==================