FR, SQL backend and Foreign keys

[Fabien COMBERNOUS]

On 27/09/2011 11:25, Alan DeKok wrote:
> Fabien COMBERNOUS wrote:
>> In the samples schema.sql proposed for SQL backend, no foreign keys are
>> used. Any reason ?
>    The schema is designed to be simple.  What foreign keys would you
> propose it use?

here a patch proposed :

--- schema_orig.sql    2011-09-28 10:42:08.000000000 +0200
+++ schema_withFK.sql    2011-09-28 10:46:42.000000000 +0200
@@ -63,8 +63,8 @@
    op char(2) NOT NULL DEFAULT '==',
    value varchar(253) NOT NULL default '',
    PRIMARY KEY  (id),
-  KEY username (username(32))
-) ;
+  KEY username (username)
+) ENGINE=InnoDB;

  #
  # Table structure for table 'radgroupcheck'
@@ -117,8 +117,10 @@
    username varchar(64) NOT NULL default '',
    groupname varchar(64) NOT NULL default '',
    priority int(11) NOT NULL default '1',
-  KEY username (username(32))
-) ;
+  INDEX username (username),
+  FOREIGN KEY (`username`) REFERENCES radcheck(username)
+    ON DELETE CASCADE ON UPDATE CASCADE
+) ENGINE=InnoDB;

-- 
*Fabien COMBERNOUS*
/unix system engineer/
www.kezia.com <http://www.kezia.com/>
*Tel: +33(0)9 5279 5202*
Kezia