CAP stands for Consistency, Availability and Partition Tolerance and specifies one can have only two of the above three attributes in a distributed data store system such as an SQL or NoSQL system. The Designer or Architect has to pick the poison based on the requirements. Further explanation of CAP attributes follows.
If one desires high availability (A-Availability) of a system, one need to have partitioning – more than 1 server. When there are multiple servers, the connectivity between them (to sync data) may become broken (a.k.a. network partition) occasionally. While the connectivity between the servers is broken, the servers may not have their data synched across each other. This leads to data inconsistency, when read from a server that has stale data, which will eventually become consistent (C-Consistency) when the network connectivity recovers.
Hence, given a highly available multi server data store system, you can only have one of Consistency(C) or Partition Tolerance(P), not both.
This kind of reminds me of the choices we have while doing things – Cheap, Good and Fast.