Referential Integrity - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Referential Integrity

Author

Topic

newB
Starting Member

2 Posts

Posted - 2011-02-22 : 18:03:16

Anyone who can help me?

Im using MAMP with terminal.

First I created tables like this:
mysql> CREATE TABLE clients(
-> client_ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> date_added DATETIME NOT NULL,
-> billing_name VARCHAR(20));
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE TABLE venues(
-> venue_ID MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
-> venue_name VARCHAR(20) NOT NULL,
-> client_ID MEDIUMINT NOT NULL,
-> FOREIGN KEY(client_ID)REFERENCES clients(client_ID));
Query OK, 0 rows affected (0.38 sec)

Then I realized referential integrity was not working so I researched online and realized that I had to add a few things so I did:

mysql> ALTER TABLE venues DROP FOREIGN KEY client_ID;
Query OK, 0 rows affected (0.13 sec)
Records: 0 Duplicates: 0 Warnings: 0

mysql> ALTER TABLE venues ADD FOREIGN KEY(client_ID) REFERENCES clients(client_ID) ON DELETE CASCADE ON UPDATE CASCADE;
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0

BUT there is still no referential integrity - this is what I get - client_ID in clients Table is the primary key and client_ID in venues is the foreign key but there is no referential integrity.

mysql> SELECT * FROM clients;
+-----------+---------------------+--------------+
| client_ID | date_added | billing_name |
+-----------+---------------------+--------------+
| 1 | 2011-02-22 13:20:45 | NULL |
+-----------+---------------------+--------------+
1 row in set (0.02 sec)

mysql> SELECT * FROM venues;
+----------+-------------+-----------+
| venue_ID | venue_name | client_ID |
+----------+-------------+-----------+
| 2 | Versailles2 | 0 |
+----------+-------------+-----------+
1 row in set (0.00 sec)

Any tips would be greatly appreciated!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-02-22 : 18:06:44

1. SQLTeam is a Microsoft SQL Server site, we don't usually answer MySQL questions. Try http://dbforums.com/
2. Make sure your tables are using InnoDB, as it's the only engine that supports referential integrity (to my limited knowledge anyway)
3. If referential integrity is actually important to you, dump MySQL entirely and use PostGreSQL.

newB
Starting Member

2 Posts

Posted - 2011-02-22 : 18:11:40

Ok thanks for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources