Databases allow bad foreign keys from Rails Fixtures

Question

Hi,

I am using Rails Fixtures to load some test data to my database and accidentally I introduced a foreign key out of range.

To my surprise, the database accepted it despite having referential integrity constraints (that work). I tried with PostgreSQL and with MySQL InnoDB and both allowed.

Example:

Having in the database "Flavours" whith a numeric primary key (id), 5 entries (1 to 5). I can introduce bad data doing:

Icecream_1: name: my ice cream flavour_id: 6

How is it possible that the fixtures loading go around my database constraints?

Thank you.

---

Here are two tables. Having 200 user_types (fake data) I was able to introduce a user with user_type_id 201 but only from fixtures, pgAdmin forbids it.

CREATE SEQUENCE user_types_id_seq;
CREATE TABLE user_types (
id SMALLINT
  NOT NULL
  DEFAULT NEXTVAL('user_types_id_seq'),
name VARCHAR(45) 
  NOT NULL 
  UNIQUE,
PRIMARY KEY (id));

CREATE SEQUENCE users_id_seq;
CREATE TABLE users (
id BIGINT
    NOT NULL
    DEFAULT NEXTVAL('users_id_seq'),
user_type_id SMALLINT
    NOT NULL
    REFERENCES user_types (id) ON DELETE CASCADE ON UPDATE CASCADE,
PRIMARY KEY (id));


---------

Fixture

<% for i in (1..201) %>

user_<%= i %>:
    id: <%= i %>
    user_type_id: <%= i %>
<% end %>

And as I said, both innoDb and postgresql accepted the bad key.

Thanks

Answer 1

PostgreSQL doesn't accept corrupt data, don't worry. In MySQL it all depends on the engine (must be innoDB) and the (connection) settings for the parameter foreign_key_checks.

How do your tables and constraints look like? Check pgAdmin (or some other client) and dump the relevant piece of datamodel over here, than we can help you out.

pgAdmin forbids it.

No, your PostgreSQL database forbids it. pgAdmin is just a client and it only sends a query to the database. The database does some checks, FK got violated and returns an error.

Looks like you're working on the wrong database (no FK's or MySQL with the wrong engine and/or settings), PostgreSQL works fine when having a FK.

Answer 2

I agree with Frank. Your test database for PostgreSQL is most probably not setup correctly. You either forgot to create the FK constraints or you disabled them.

The fact that you got an error in pgAdmin indicates that you are working with a different database from within pgAdmin and your test script.

As far as MySQL is concerned I'd look for a wrong default engine in the test database or if you also forgot to create the FK constraints there (note that you will not get an error if you create a FK constraint with an engine that doesn't support referential integrity on MySQL)

Answer 3

Check the table definitions in your test database. IIRC, "rake db:test:prepare" does not maintain fidelity when creating the tables in the test database.

Answer 4

Thank you all for answering.

Someone at ruby forum figured it out. Looks like the triggers which enforce RI are disabled prior to the loading of the fixtures.

I don't know why but it solves the mistery.