CODEWITHSUNDEEP
mysqldatabaseschemamysql-workbench
Pol
Pol

Reputation: 325

Is cardinality a database constraint?

Assume I have this schema:

enter image description here

Now, this tells me an owner can only have, at best, one pet. However, for the longest time I thought if I were to have an entry like this:

idpet: 1, owner_idowner:1

and I were to add another entry like:

idpet: 2, owner_idowner:1

the database itself wouldn't let me (so it'd act like a FK constraint or PK constraint).

However, this doesn't seem to be the case, as something like this seems to be accepted by MySQL. Therefore, is cardinality not an actual constraint? Is it just a rule I should keep in mind when adding entries and the database does nothing to maintain it? I always thought cardinality as a constraint up until now, basically, but this is very wrong, most likely.

Upvotes: 1

Views: 63

Answers (1)

GenYMaverick
GenYMaverick

Reputation: 46

You can use the following SQL query to reach the expected result.

CREATE TABLE pet (
    idpet                 INT, 
    owner_idowner         INT, 
    UNIQUE(owner_idowner)
);

If you now try to insert data with the following query then the second one will result in an error.

INSERT INTO pet VALUES(1,1);
INSERT INTO pet VALUES(2,1);

Upvotes: 2

Related Questions